On 1/19/2011 12:19 PM, Noah Hart wrote:
> I am having difficulty trying to return data in a very compact form. Here
> is
> a simple example that will explain the problem:
>
> I have a table with column E, T and P containing data such as
> A, 1, R
> A, 1, S
> A, 2, R
> A, 2, S
>
> Trying the query
> select E, group_concat(T,';'), group_concat(P,';') give me the expected
> results
> A 1;1;2;2 R;S;R;S
>
> However, I would like to create a query will return the results
>
> A 1;2 R;S
select E, replace(group_concat(distinct T), ',', ';'),
replace(group_concat(distinct P), ',', ';') from MyTable;
Wouldn't work if values in T or P contain commas.
Here's a more verbose query that doesn't have this limitation (but is
likely much slower):
select E,
(select group_concat(T, ';') from (select distinct T from MyTable
where E=AllE.E)),
(select group_concat(P, ';') from (select distinct P from MyTable
where E=AllE.E))
from (select distinct E from MyTable) AllE;
--
Igor Tandetnik
_______________________________________________
Ahhh the DISTINCT keyword was what I'm missing.
However, this may be a bug, when I use group_concat(DISTINCT T, ';') I get
the error message
DISTINCT aggregates must have exactly one argument
Noah
--
View this message in context:
http://old.nabble.com/how-to-use-group_concat-uniquely-tp30712025p30712310.html
Sent from the SQLite mailing list archive at Nabble.com.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
