[sqlite] Can this be done in SQLite?

[R.Smith]

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