get all row values in single record

Question

i have a table in which i want to get column values into single record separated by comma

output should be col1 col2 col3 1 roy a,f,g,h 2 sam h,k,i 3 joe q,w,e,r,t,y — Pranay Kumar, May 16 '17 at 16:10
Possible duplicate of [How Stuff and 'For Xml Path' work in Sql Server](http://stackoverflow.com/questions/31211506/how-stuff-and-for-xml-path-work-in-sql-server) — Gurwinder Singh, May 16 '17 at 16:13

score 4 · Accepted Answer · answered May 16 '17 at 16:13

using the stuff() with select ... for xml path ('') method of string concatenation.

select col1, col2, col3 = stuff(
  (
  select ','+i.col3
    from t as i
    where i.col1 = t.col1
  for xml path (''), type).value('.','nvarchar(max)')
  ,1,1,'')
from t
group by col1, col2

rextester demo: http://rextester.com/QXH88855

returns:

+------+------+-------------+
| col1 | col2 |    col3     |
+------+------+-------------+
|    1 | roy  | a,f,g,h     |
|    2 | sam  | h,k,l       |
|    3 | joe  | q,w,e,r,t,y |
+------+------+-------------+

score 2 · Answer 2 · answered May 16 '17 at 17:36

2

If SQL Server 2017 or Vnext or SQL Azure you can use string_agg

SELECT col1, col2, STRING_AGG(col3, ',') from yourtable
   GROUP BY col1, col2

answered May 16 '17 at 17:36

Kannan Kandasamy

13,405
3
25
38

thanks but im using 2014 SQL. i can use above one... thanks anyways – Pranay Kumar May 17 '17 at 20:09

score 0 · Answer 3 · answered May 16 '17 at 16:13

Formatting the output really should be done in the program that receives the data. You can export from SQL Server Management studio to csv by selecting "output to file" on the toolbar and configuring comma delimited output.

If you really need to combine the columns into a comma separated single value:

SELECT CAST(col1 AS NVARCHAR(100)) + N',' + CAST(col2 AS NVARCHAR(100)) + N',' + CAST(col3 AS NVARCHAR(100)) FROM table

get all row values in single record

3 Answers3