Transpose a query result

[anuragshrivastava64]

I have a table which contains ID and Email col.
The data is stored as follows:
ID Email
---------------
1 A
2 A0
2 A1
3 A2
3 A3
3 A4
4 A5

I want to show the value as

ID Email1 Email2 Email3
1 A
2 A1 A0
3 A2 A3 A4
4 A5

Is there any way possible to do this

[gpl]

See my response (#2) in http://bytes.com/topic/sql-server/an...end-fields-sql
this will very nearly do what you want, otherwise look here: http://www.sqlteam.com/search.aspx?c...=crosstab#1161 which is a search page with links to articles about doing crosstabs

Good Luck
Graham

[ck9663]

Here's something that will not require dynamic sql...

declare @tbl_email table (id int, email varchar(20))
insert into @tbl_email 
select 1, 'A'
union all
select 2, 'A0'
union all
select 2, 'A1'
union all
select 3, 'A2'
union all
select 3, 'A3'
union all
select 3, 'A4'
union all
select 4, 'A5'

select * from @tbl_email

;with Numbered
as 
   (
      select       
         rownum = row_number() over(partition by id order by id, email),
         id, email
      from @tbl_email
   )
,FirstRecords
as 
   (
      select rownum, id, email from Numbered where rownum = 1
   )
,ReturnAllRecords
as
   (
      select rownum, id, cast(email as varchar(500)) as email_list from FirstRecords  
      union all      
      select Numbered.rownum, Numbered.id,  cast(email_list + ', ' + Numbered.email as varchar(500))
      from Numbered
         inner join ReturnAllRecords 
            on ReturnAllRecords.id = Numbered.id and
               ReturnAllRecords.rownum + 1 = Numbered.Rownum
   )
select id, max(email_list)
from ReturnAllRecords
group by id

Make sure to create the necessary index.


Happy Coding!!!

~~ CK