T-SQL – How to select multiple rows into a single row

I spend too long on finding the solution for this problem…

Basically I had a table which looked like this
table T SQL   How to select multiple rows into a single row

What I wanted was to select all as one row as a set of items separated with a comma, where an item was ‘id: name’. The items had to be sorted by the id. So the result was supposed to look as below:
result T SQL   How to select multiple rows into a single row

Solution

Initially I tried to write one SELECT:

SELECT
STUFF
(
	(
	SELECT ', ' + cast(id AS NVARCHAR(max)) + ': ' + name
	FROM People
	ORDER BY Id
	FOR XML PATH ('')
	),1,1,''
)

The problem here was FOR XML PATH escaped characters that could break the XML (e.g. ‘<' was replaced with '<'), which was not acceptible for me.

I looked for another solution but whatever I tried didn't work because of the ORDER BY statement which can't be used in a subquery or inline view.
Finally I decided to use a temporary table filling it with correct data and then create the final result. Here is the code:

DECLARE @res NVARCHAR(max)
SET @res = ''

CREATE TABLE #Result ( item nvarchar(max) )

INSERT INTO #Result
SELECT CAST(Id AS NVARCHAR(max)) + ': ' + Name AS item
FROM People
ORDER BY id

SELECT @res = @res + item + ', ' from #Result
SELECT substring(@res,1,len(@res)-1) as Result

If you find a better solution can you please post it as a comment? I could use in in future icon smile T SQL   How to select multiple rows into a single row Thanks in advance!

11 Responses to “T-SQL – How to select multiple rows into a single row”


  • You could always stream it as XML Data and convert it into one column that way, similar to what you started with:

    ISNULL(LTRIM(RTRIM(REPLACE(
    (
    SELECT CAST(ID AS NVARCHAR(MAX)) + ‘: ‘ + NAME AS [data()]
    FROM People
    ORDER BY ID
    FOR XML PATH (”)
    ), ”, ‘,’))), ”)

    For instances where it might “replace” strings with things like >… you could always encapsulate in a REPLACE string to change it back?

    I use this trick a lot, but all the data I pass to it is alphanumeric with no symbols or unicode characters.

  • So true, you can always replace parts of the string… Sometimes easy solutions are the hardest to find :)

  • Cool, thanks for this guys, I got stuck with something like this a while back. I could not do it with SQL, I had to move the formatting to my application.

  • — HOW ABOUT USING CURSORS??:

    — SELECT ONE COLUMN —
    DECLARE @colvalu varchar (150),
    @cString1 varchar (MAX),
    @cString2 varchar (MAX)
    SET @cString2 = ”

    DECLARE my_cursor CURSOR FOR
    select distinct COLNAME from TEBLENAME
    OPEN my_cursor

    FETCH NEXT FROM my_cursor
    INTO @colvalu

    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @cString1 = @colvalu + ‘,’
    FETCH NEXT FROM my_cursor
    INTO @colvalu
    SET @cString2 = @cString2 + @cString1
    END
    SET @cString2 = Left(@cString2, Len(@cString2)-1)
    print @cString2
    CLOSE my_cursor
    DEALLOCATE my_cursor

    — SELECT 2(+) COLUMNS —
    DECLARE @colvalu_1 varchar (150),
    @colvalu_2 varchar (150),
    @cString1 varchar (MAX),
    @cString2 varchar (MAX)
    SET @cString2 = ”

    DECLARE my_cursor CURSOR FOR
    SELECT COLNAME_1, COLNAME_2 FROM DBNAME
    OPEN my_cursor

    FETCH NEXT FROM my_cursor
    INTO @colvalu_1, @colvalu_2

    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @cString1 = @colvalu_1 + @colvalu_2 + ‘,’
    FETCH NEXT FROM my_cursor
    INTO @colvalu_1, @colvalu_2
    SET @cString2 = @cString2 + @cString1
    END
    SET @cString2 = Left(@cString2, Len(@cString2)-1)
    print @cString2
    CLOSE my_cursor
    DEALLOCATE my_cursor

  • Cursors are an option here but I didn’t want to use them. Not only is it (much) longer but also not that efficient (especially if a table holds lots of data).

  • Simple code for a simple problem. This is an oldie….

    declare @RESULT NVARCHAR(max)
    set @RESULT = ”

    SELECT @RESULT = @RESULT + CASE @RESULT WHEN ” THEN ” ELSE ‘, ‘ END + cast(id as NVARCHAR(MAX)) + ‘: ‘ + ISNULL(NAME,’ ‘)
    FROM people
    ORDER BY id

  • Can try use my code if you are interested.

    Sorry if it is abit messy, I’m new to SQL and I’m not particularly good at coding.

    ——————————————————————–
    declare @Counter int
    declare @RowCount int
    declare @RESULT NVARCHAR(max)
    declare @TempRESULT NVARCHAR(max)
    declare @Table Table (
    internalID int identity(1,1),
    id int,
    Name varchar(max)
    )

    insert @table (id, Name)
    select id, Name from People
    order by id asc

    set @Counter = 0
    set @RESULT = ”
    select @RowCount = COUNT(internalID) from @Table
    while @Counter < @RowCount
    begin
    set @Counter = @Counter + 1
    Select @TempRESULT = cast(id as varchar(max)) from @Table where internalID = @Counter
    select @TempRESULT = @TempRESULT + ':' + Name from @Table where internalID = @Counter
    if (@Counter = 1)
    begin
    set @RESULT = @TempRESULT
    end
    else
    begin
    set @RESULT = @RESULT + ', ' + @TempRESULT
    end
    end

    ——————————————————————

    It should work. I modified it abit from my codings to this.

  • I have one table named Fixture which contains a column FixtureID. For instance, I want to get top 5 FixtureID and pass it to another query like comma separated value, but the comparison would be with BIGINT field like;

    CREATE TABLE Fixture (FixtureID BIGINT)

    INSERT INTO Fixture(FixtureID) VALUES(11111111)
    INSERT INTO Fixture(FixtureID) VALUES(22222222)
    INSERT INTO Fixture(FixtureID) VALUES(33333333)
    INSERT INTO Fixture(FixtureID) VALUES(44444444)
    INSERT INTO Fixture(FixtureID) VALUES(55555555)

    DECLARE @res NVARCHAR(max)
    SET @res = ”

    SELECT TOP 5 @res = @res + FixtureID + ‘, ‘ from Fixture
    SELECT @res = substring(@res,1,len(@res)-1)

    SELECT * FROM Match
    WHERE FixtureID IN (@res)

    FixtureID is BIGINT, how should I compare comma separated values with BIGINT column?

  • Thanks a lot Jarosław, your page saved me a lot of time!

    Didn’t need as sophisticated solution as you and what Chris suggested was perfect for my needs :)

  • Thank you very much,Jarosław.The blog helps me a lot.
    Though there are many good solutions,I like Chris better.Meantime I think that I find a better solution which follows Chris’s good idea.

    declare @RESULT nvarchar(max)
    set @RESULT = ”
    select @RESULT = @RESULT + cast(id as NVARCHAR(MAX)) + ‘: ‘ + ISNULL(NAME,”) + ‘,’
    FROM people
    ORDER BY id

    –delete the last comma
    set @RESULT = substring(@RESULT,1,len(@RESULT)-1)

    Thanks for your attention.

Leave a Reply