SQLTeam.com | Weblogs | Forums

Multiple row data in one row based on some column


#1

Hi Team, I have following data and i need to write a query to show data in a maaner where all related id rows will be show in single row.

declare @tbl table (id int, col1 varchar(10), col2 int)

insert into @tbl
values(1,'abc',11),(1,'xyz',22),(1,'jkl',23),(2,'ghu',12),(2,'dfr',14),(3,'cv',12),(3,'rf',13),(3,'fkl',90),(3,'ddd',90)

select * from @tbl

sample output:
col1 col2 col3 col4 col5 col6 col7 col8 col9
1 abc 11 xyz 22 jkl 23 null null
2 ghu 12 dfr 14 null null null null
3 cv 12 rf 13 fkl 90 ddd 90

please help how to achieve this.


#2

For COLUMNS then PIVOT - but not straightforward without "keys" on the child records that map only the COL1, COL2, ... names

For a simple "delimited list" (i.e. all concatenated, delimited with COMMA / TAB / whatever, but in effect in a SINGLE column) then the XML trick that will make a list from child rows.

If the second will do? I can point you in the direction of the "XML trick"