Insert dynamic rows to a single column

Question

Possible Duplicate:
Simulating group_concat MySQL function in Microsoft SQL Server 2005?

How will I insert rows (which are dynamic) to a single column concatenated by space to a temp table. I'm using SQL Server 2005.

Ex. LetterTable:

LetterID LetterRow
    1        a
    2        b
    3        c
    4        d
    5        e

to insert to a temp table like

#TempTable:

LetterID LetterRow
    1     a b c d e

Thanks in advance.

score 1 · Answer 1 · answered Oct 30 '12 at 05:53

well I don't quite understand, why your LetterID equals 1 in first example, but you can try this (solution like this is useful for concatenation in grouped rows)

insert into #TempTable (LetterID, LetterRow)
select
    min(L.LetterID) as LetterID,
    stuff(
        (
            select ' ' + T.LetterRow
            from LetterTable as T
            for xml path(''), type
        ).value('.', 'nvarchar(max)')
   ,1,1, '') as LetterRow
from LetterTable as L

for one row you can also write this:

declare @LetterRow nvarchar(max), @LetterID int

select @LetterID = min(LetterID) from LetterTable
select @LetterRow = isnull(@LetterRow + ' ', '') + LetterRow from LetterTable

insert into #TempTable (LetterID, LetterRow)
select @LetterID, @LetterRow

Insert dynamic rows to a single column

1 Answers1