SQL Transform duplicates to one row

Question

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

id	code	date
1527	26181	16/03/21
1527	15182	05/09/20

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

id	code
1527	26181,15182

Glad for any help

Why do you want to store multiple values in a single field? That's a SQL Anti-Pattern, *generally* indicative of a faulty design. Avoid comma delimited values whenever possible. — MatBailie, Nov 07 '21 at 15:08
T-SQL now has the STRING_AGG() function which is available from SQL Server 2017. This function does pretty much the same thing as MySQL’s GROUP_CONCAT() function (with some minor differences). — Halley Oliveira, Nov 07 '21 at 15:09
Follow the examples in the docs: https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15 — David Browne - Microsoft, Nov 07 '21 at 15:13
Does this answer your question? [Simulating group\_concat MySQL function in Microsoft SQL Server 2005?](https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) In particular this answer https://stackoverflow.com/a/40692104/14868997 — Charlieface, Nov 07 '21 at 15:33

score 1 · Accepted Answer · answered Nov 07 '21 at 15:19

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

SQL Transform duplicates to one row

1 Answers1