Convert column resultset to row SQL Server

Question

I'm new to StackOverflow and this is my first post.

Here is what I want to achieve, I want to print all the names where id = 1 in a single row

Here is a result I want to achieve for Id = 1 query

Id Names 1 Tom Smith, Paul Walker, Peter Robin

Here is the sample table

CREATE TABLE myData(
ID INT NOT null, 
FirstName NVARCHAR(20),
LastName NVARCHAR(20)
)

INSERT INTO myData
VALUES(1,'Tom','Smith')
INSERT INTO myData
VALUES(2,'Jared','Williams')
INSERT INTO myData
VALUES(1,'Paul','Walker')
INSERT INTO myData
VALUES(4,'Mary','Elisabeth')
INSERT INTO myData
VALUES(1,'Peter','Robin')
INSERT INTO myData
VALUES(2,'Chen','Leo')

Do you know how many there will be (a max number of records returned)? You need to look into PIVOT. Most likely dynamic PIVOT based off the answer to the number of possible records returned. Also question is why do you need it this way? — Brad, Feb 25 '19 at 20:17

Derviş Kayımbaşıoğlu · Accepted Answer · 2019-02-25T20:43:25.673

0

try string_agg with group by

select id, string_agg(Concat(FirstName, ' ', LastName),',')
from myData
group by id

For old versions of Sql Server

SELECT ID,  result = STUFF(
   (SELECT ',' + Concat(FirstName, ' ', LastName)
    FROM myData t1
    WHERE t1.id = t2.id
    FOR XML PATH (''))
   , 1, 1, '') from myData t2
group by id;

edited Feb 25 '19 at 20:43

answered Feb 25 '19 at 20:17

Derviş Kayımbaşıoğlu

28,492
4
50
72

Sorry, I forgot to mention in the post. I'm using SQL Server 2012 and it does not have built-in string_agg Function – CodeForGood Feb 25 '19 at 20:24
please check my answer again – Derviş Kayımbaşıoğlu Feb 25 '19 at 20:44
That worked! Thanks a lot – CodeForGood Feb 25 '19 at 21:25

Convert column resultset to row SQL Server

1 Answers1