sql - Getting Rows in the form of Column Names -

input table format in sql server 2008 :

            *name*                                 *department*  1)          abcd                                    2)          abcd                                    ctech 3)          abcd                                    mech 4)          uvw                                     sap 5)          uvw                                     informatica 

desired output result:

         name     department1      department2 1)       abcd                   ctech 2)       uvw       sap             informatica 

above scenario sql server 2008 table...

can me output result format shown?

there several ways transform data columns.

you can use aggregate function case expression:

select name,   max(case when rn = 1 department end) department1,   max(case when rn = 2 department end) department2,   max(case when rn = 3 department end) department3 (   select name,     department,     row_number() over(partition name order department) rn   yt ) src group name; 

see sql fiddle demo

you use pivot tranform data rows columns.

select * (   select name,     department,     'department'         +cast(row_number() over(partition name order department) varchar(10)) col   yt ) src pivot (   max(department)   col in ([department1], [department2], [department3]) ) piv; 

see sql fiddle demo

i suggest using dynamic sql result since might have unknown number of departments per name. dynamic sql code be:

declare @cols nvarchar(max),     @query  nvarchar(max)  select @cols = stuff((select distinct ',' + quotename(col)                                          (                       select 'department'+                         cast(row_number() over(partition name order department) varchar(10)) col                       yt                     ) src             xml path(''), type             ).value('.', 'nvarchar(max)')          ,1,1,'')  set @query = 'select name, ' + @cols + '                            (                 select name, department,                   ''department''+                         cast(row_number() over(partition name order department) varchar(10)) col                 yt             ) x             pivot              (                 max(department)                 col in (' + @cols + ')             ) p '  execute(@query); 

see sql fiddle demo