CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Aditya Pratap Singh
Aditya Pratap Singh

Reputation: 108

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

Upvotes: 1

Views: 473

Answers (2)

Kirk Broadhurst
Kirk Broadhurst

Reputation: 28718

Adam's answer will work, but the most correct part of his answer is

you may be better off handling this in your UI / Display tier

What you are trying to do is - to put it bluntly - wrong. The below says, for example, that aditya has class mca, but abhishek and akhilesh don't have a class.

class    name      sub 
--------------------- 
mca      aditya    network          
         abhishek  daa          
         akhilesh  algorithm 
btech    ram       cs              
         shyam     ds
         anand     client/server

Is it true that abhishek and akhilesh don't have a class? Of course not.

So if I have an application that calls this stored procedure or view and looks at this resultset, how am I supposed to know which class shyam belongs to? You can't say 'it's btech because that's the last class mentioned', because how do you know which order the results are viewed? You have no order by in your query.

Upvotes: 0

Adam Wenger
Adam Wenger

Reputation: 17540

I think you may be better off handling this in your UI / Display tier, but if you need the query to do it in SQL 2008, you can use a CTE with ROW_NUMBER() 

WITH ordered AS
(
   SELECT t.class, t.name, t.sub
      , ROW_NUMBER() OVER (PARTITION BY t.class ORDER BY t.Name ASC) AS RowNumber
   FROM myTable AS t
)
SELECT CASE 
          WHEN o.RowNumber = 1 THEN o.class
          ELSE ''
       END AS class
   , o.name, o.sub
FROM ordered AS o

I'm not sure what sort / order you're looking for. Your example appears to still be ordered by Id. If you want this, you can add the Id column into the ordered CTE and then add ORDER BY o.Id ASC at the end of the query.

Upvotes: 3

Related Questions