CODEWITHSUNDEEP
sqlsql-server
Steven Chou
Steven Chou

Reputation: 2215

SQL : how to translate rows to one field?

as title,

SQL:

select * from table

data:

id     b    c   d   

4      17   Q   1   
5      17   Z   9   
6      17   G   5   
7      18   Q   3

I need following result:

b    field

17   Q:1,Z:9,G:5
18   Q:3

how can I change the SQL statement? thanks !

Upvotes: 0

Views: 77

Answers (1)

thepirat000
thepirat000

Reputation: 13114

Like this:

SELECT  b, STUFF((select ', ' + c + ': ' + cast(d as varchar)
        FROM table xt
        WHERE xt.b = t.b
        FOR XML PATH('')), 1, 2, '')
FROM table t
GROUP BY b

Upvotes: 1

Related Questions