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

Question

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?

let me get this straight, you want to concatenate some int values onto a single column, but you want them to remain as ints? — Lamak
– Lamak, Commented Mar 30, 2016 at 15:57
You will have to cast/convert them to varchar so they are treated as strings. After that you could probably do what you did for the Name column to get it to work. — Igor
– Igor, Commented Mar 30, 2016 at 15:59
If you want to concatenate NameIDs as a list separated by comma, you may agree with me, that a comma separated list of integers is not an integer. — alessalessio
– alessalessio, Commented Mar 30, 2016 at 16:04
I need all the cells that have int to remain as int - Why? What good will it do to keep the column type as int (which is not possible)? Could you execute math operation on it or add them together or something else (rhetorical question, answer is no). Those are reasons to keep a value numeric. Now for the sake of argument lets assume it is possible, a concatenated list of ints in a single cell is useless in sql, you can't do anything with it or materialize it out of sql (lets say to an ORM). — Igor
– Igor, Commented Mar 30, 2016 at 16:08
Because I have a asp.net application that has a drop down list with the Names and NameIDs and when a user chooses an option, it will filter the grid with above information by the NameID. If there is a way to still filter by NameID and have it as a string, then please show me how. Any help is much appreciated. — bds89
– bds89, Commented Mar 30, 2016 at 16:13

EarlOfEnnui · Accepted Answer · 2016-03-30 18:17:20Z

2

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

answered Mar 30, 2016 at 18:17

EarlOfEnnui

5554 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related