Thursday, December 15, 2011

showing column data as header in sql server 2008

I have a table like:
id  class name      sub
1   mca   aditya    network 
2   mca   abhishek  daa
3   mca   akhilesh  algorithm
4   btech ram       cs
5   btech shyam     ds 
6   btech anand     client/server
7   mba   furqan    os 
8   mba   arvind    marketing 
9   mba   aayush    hr
I want a result set like the following:
class    name      sub
mca      aditya    network
         abhishek  daa
         akhilesh  algorithm
btech    ram       cs
         shyam     ds
         anand     client/server
mba      furqan    os
         arvind    marketing
         aayush    hr
WITH ordered AS
   SELECT t.class,, t.sub,
  (PARTITION BY t.class ORDER BY t.Name ASC) AS RowNumber
   FROM myTable AS t)
          WHEN o.RowNumber = 1 THEN o.class
          ELSE ''
       END AS class,, o.subFROM ordered AS o 

1 comment: