0

I am trying to format the data from a select query from different tables, the query, i have gets me this:

Column1     Column2     Column3
123         Mark        INV001
123         Mark        INV002
456         Smith       INV003

Expected result:

Column1     Column2     Column3

123          Mark       INV001,INV002
456          Smith      INV003

I tried this query, but doesn't yield the expected result, any help would be appreciated.

WITH myCTE (

                SELECT column1,column2,column3 FROM table1;
                )
                SELECT column1, column2, STUFF((SELECT ', ' + CAST(column3 AS VARCHAR(50)) [text()]
                        FROM myCTE 
                        FOR XML PATH(''), TYPE)
                        .value('.','NVARCHAR(MAX)'),1,2,' ') column3
                        FROM myCTE;
Sharpeye500
  • 8,775
  • 25
  • 95
  • 143
  • Google: "SQL Server string aggregation" – Gordon Linoff Sep 16 '16 at 20:00
  • Have a look at this post: http://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server – Flo Sep 16 '16 at 20:01
  • http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string and http://stackoverflow.com/questions/11351076/sql-concatenate-column-values-in-a-single-row-into-a-string-separated-by-comma could be of use for you. Cheers! – S3S Sep 16 '16 at 20:01

0 Answers0