Combining multiple columns of a table into single column, where first column value determines new rows

Question

Basically I need to take 3 columns from a table and combine them into rows of strings using a the value of the first column.

Starting basic output using:

    select ItemID, Month, Year
      from tableA 
        where ID = @id

Output:

ItemID Month Year

 1. 4 1 2012
 2. 4 2 2012
 3. 4 3 2012
 4. 4 6 2012
 5. 4 8 2012
 6. 12 1 2012
 7. 12 2 2012
 8. 12 4 2012
 9. 12 5 2012
 10. 12 6 2012
 11. 12 7 2012
 12. 53 8 2012
 13. 53 9 2012

The output I am trying to get should look something similar to this:

 1. 41201222012320126201282012
 2. 12120122201242012520126201272012
 3. 538201292012

Basically, combining all the ItemID's of the same number and adding the month and year to the end of that line until no more, then starting a new line when there is a new ItemID.

I have tried various temp table and pivot's but cannot manage to get this to output correctly. Any help would be appreciated.

Thanks

So, using this:

    declare @table table (idmy varchar(8))

insert into @table
    select cast(ItemID as varchar(2)) + 
        cast(Month as varchar(2)) + 
        cast(Year as varchar(4)) as idmy
        from TableA
            where ID = @id

select idmy from @table

I can get an output of:

idmy
 1. 412012
 2. 422012
 3. 432012
 4. 462012
 5. 482012
 6. 1212012
 7. 1222012
 8. 1242012
 9. 1252012
 10. 1262012
 11. 1272012
 12. 5382012
 13. 5392012

Now to combine all like initial rows while doing above...

possible duplicate of [Simulating group_concat MySQL function in MS SQL Server 2005?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-ms-sql-server-2005) — Martin Smith, Jan 13 '12 at 20:48

score 0 · Answer 1 · answered Jan 13 '12 at 22:17

select 
coalesce(cast(itemid as nvarchar(4)),'') +
coalesce(cast([1] as nvarchar(500)),'') +
coalesce(cast([2] as nvarchar(500)),'') +
coalesce(cast([3] as nvarchar(500)),'') +
coalesce(cast([4] as nvarchar(500)),'') +
coalesce(cast([5] as nvarchar(500)),'') +
coalesce(cast([6] as nvarchar(500)),'') +
coalesce(cast([7] as nvarchar(500)),'') data
from 
(select itemid, 
    cast(Month as varchar(2)) + 
    cast(Year as varchar(4)) as idmy,
    row_number() over (partition by itemid order by cast(ItemID as varchar(2)) + cast(Month as varchar(2)) + cast(Year as varchar(4))) rownum
from test
) f
pivot (max(idmy) for rownum in ([1], [2], [3], [4], [5], [6], [7])) p

I used the row_number windowing function to get order each row and then pivot each one to its own column which then gets converted to a varchar above which is concatenated after being grouped by itemid

This answer almost works, with the exception of repeating the ItemID at every row insert, instead of just at the start or each row. It is helpful though for sure, thanks for the response. — Dan Williams, Jan 13 '12 at 22:33
I've edited it to only enter the itemid at the beginning. The limitation here is that you'll need to expand on the pivot statement and try to anticipate the maximum number of rows per item. Currently you're capped at 7 rows per item. — Adrian, Jan 13 '12 at 23:48

score 0 · Accepted Answer · answered Jan 13 '12 at 22:24

create table #tableA
(ItemID int, Month int, Year int)

insert into #tableA
values (4, 1, 2012)
insert into #tableA
values (4, 2, 2012)
insert into #tableA
values (4, 2, 2012)
insert into #tableA
values (4, 3, 2012)
insert into #tableA
values (4, 4, 2012)
insert into #tableA
values (4, 6, 2012)
insert into #tableA
values (4, 8, 2012)
insert into #tableA
values (12, 1, 2012)
insert into #tableA
values (12, 2, 2012)
insert into #tableA
values (12, 4, 2012)
insert into #tableA
values (12, 6, 2012)
insert into #tableA
values (12, 7, 2012)
insert into #tableA
values (53, 8, 2012)
insert into #tableA
values (53, 9, 2012)



declare @results as table
(line varchar(max))

declare @currentItemId int
set @currentItemId = -1

declare @str varchar(max)

while(@currentItemId is not null)
begin

    set @str = ''

    select @currentItemId=min(ItemID)
    from #tableA
    where @currentItemId < ItemID

    if(@currentItemId is null)
        break

    select @str=@str+cast(Month as varchar)+cast(Year as varchar)
    from #tableA
    where ItemID = @currentItemId
    order by Year, Month

    insert into @results
    select cast(@currentItemId as varchar)+@str

end

select *
from @results

Creates:

412012220122201232012420126201282012
121201222012420126201272012
538201292012

This answer works as intended and is what I needed, it is very useful. Thank you for your response. — Dan Williams, Jan 13 '12 at 22:45
Though, my senior programmer frowned on the use of while loop on SQL SP, if anyone reads this what do you think about it (loops in sql stored procedures)? — Dan Williams, Jan 14 '12 at 00:15
Sometimes simpler is better, sometimes that involves a loop :) — JBrooks, Jan 14 '12 at 03:37
@DanWilliams - You can do this without loops using `XML PATH` as per the "possible duplicate" link. — Martin Smith, Jan 14 '12 at 13:08

Combining multiple columns of a table into single column, where first column value determines new rows

2 Answers2