Search code examples
sqlsql-serversql-server-2014

Display all the child values in a single row with , separated which has same parent id

I want a simple sql script for the following requirement. And the data may increase in future and it is not static. So I need dynamic query. Someone please help me with this.

I have a data like this

input

and the output should be like this.

output

Solution

  • Try this using Stuff() in MSSql server

    ;With cte(ParentId, ChidId)
    AS
    (
    SELECT 1,'a'     UNION ALL
    SELECT 1,'b'     UNION ALL
    SELECT 1,'123'   UNION ALL
    SELECT 2,'asd'   UNION ALL
    SELECT 2,'222'   UNION ALL
    SELECT 3,'kk'    UNION ALL
    SELECT 3,'ffh'   UNION ALL
    SELECT 4,'aaa'   UNION ALL
    SELECT 5,'fdhgf' UNION ALL
    SELECT 5,'fdddv'
    )
    SELECT parentid, 
           Stuff((SELECT DISTINCT ', ' + chidid 
                  FROM   cte i 
                  WHERE  i.parentid = o.parentid 
                  FOR xml path ('')), 1, 1, '') AS ChidId 
    FROM   cte o 
    GROUP  BY parentid