It does indeed work - had to make myself a simpler query than the real life one to see this. I probably had some other syntax error that I mistook for it... Thanks!
> To: sqlite-users at mailinglists.sqlite.org > From: rsmith at rsweb.co.za > Date: Thu, 8 Oct 2015 23:19:53 +0200 > Subject: Re: [sqlite] Can this be done in SQLite? > > > > On 2015-10-08 10:54 PM, K. P. wrote: > > Thanks for that.I'd need something along the lines of > > > > group_concat(c.LastName || ', ' || c.FirstName, ';') as ClientName, > > > > > > which in itself does not seem to be supported. > > This would be quite a normal group concatenation and works perfectly well... > > Why do you believe it doesn't work or isn't supported? > > > > c.LastName || ', ' || c.FirstName, ';') as FullName, > > group_concat(FullName, ';') as ClientName, > > Ok, this won't work, but not for the reasons you think. An alias cannot > be referenced in the same select header as it is instantiated. You can > however achieve this with a CTE (among other ways), something like this > (I made up A and B here cause I don't know the rest of your schema): > > WITH cte1(A, B, FullName) AS ( > SELECT c.A, c.B, c.LastName || ' ' || c.FirstName FROM someTable AS > cWHERE 1 > ) > SELECT A, B, group_concat(FullName, '; ') > FROM cte1 > WHERE 1 > GROUP BY A, B > ORDER BY A, B > > Perhaps you don't wish to have names repeated, in which case this will > work better: > > WITH cte1(A, B, FullName) AS ( > SELECT c.A, c.B, c.LastName || ' ' || c.FirstName FROM someTable AS > cWHERE 1 > ) > SELECT A, B, group_concat(DISTINCT FullName) > FROM cte1 > WHERE 1 > GROUP BY A, B > ORDER BY A, B > > > > If you have some SQL that doesn't seem to work, kindly post your table > schema and the full SQL you are trying to do, that way we can form a > better idea of what you aim to do and provide more complete answers. > > Cheers! > Ryan > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
