I’m having a table named TBL which has an id, code, date

When i’m using this query :

SELECT * FROM TBL 
WHERE TBL.id=“1527”

I’m getting this back

How can I turn it to this?(by query)

Glad for any help

CodePudding user response：

You can use STRING_AGG

SELECT Id,STRING_AGG(code,',')  WITHIN GROUP ( ORDER BY Id ASC)  AS
Code FROM tbl
GROUP BY Id

Also, you can try this:

SELECT  id
       ,STUFF((SELECT ', '   CAST(code AS VARCHAR(10)) [text()]
         FROM tbl 
         WHERE id= t.id
         FOR XML PATH(''), TYPE)
        .value('.','NVARCHAR(MAX)'),1,2,' ') List_Output
FROM tbl t
GROUP BY id

CodePudding user response：

use this code

SELECT DISTINCT
   tbl.id,
   STUFF((SELECT ' , '   tb.code 
          FROM TBL  tb
          WHERE tb.id = 1572
          ORDER BY code
          FOR XML PATH('')), 1, 1, '') [code]
FROM TBL   tbl
GROUP BY tbl.id, tbl.code
ORDER BY 1