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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
