CODEWITHSUNDEEP
sqlsql-servert-sql
bds89
bds89

Reputation: 165

How to concatenate many SQL rows into single text string for int

The SQL table I have is as follows:

ID     Name   NameID 
1      John   1
1      Dan    2
1      Jill   3
2      Jack   4
2      Sam    5

I need this table to be like this:

ID      Name             NameID 
1       John, Dan, Jill  1, 2, 3
2       Jack, Sam        4, 5

The SQL query that I have for now is as follows:

SELECT ID, 
    STUFF((
        SELECT N', ' + Name 
        FROM Table2 b 
        WHERE b.NameID = a.NameID 
        FOR XML PATH (''),TYPE).value('text()[1]','nvarchar(max)'
        ),1,2,N'') AS Name,
    NameID
FROM Table1 a

That query does what I need for the Name column but it won't work when I try to do it for the NameID column. I need all the cells that have int to remain as int. I cannot have them as string since I will be needing to filter the table by the NameID. How do I get all NameID into a single cell for each ID?

Upvotes: 1

Views: 1061

Answers (1)

EarlOfEnnui
EarlOfEnnui

Reputation: 555

Does this help?

SELECT DISTINCT ID,

    STUFF((
        SELECT N', ' + Name 
        FROM Table1 b 
        WHERE b.ID = a.ID 
        FOR XML PATH (''),TYPE).value('text()[1]','nvarchar(max)'
        ),1,2,N'') AS Name,

    STUFF((
        SELECT N', ' + CONVERT(VARCHAR(20), NameID) 
        FROM Table1 b 
        WHERE b.ID = a.ID 
        FOR XML PATH (''),TYPE).value('text()[1]','nvarchar(max)'
        ),1,2,N'') AS NameID

FROM Table1 a

Upvotes: 2

Related Questions