how to pivot a table in sql

Question

I created a table as follows:

create table stud(sname nvarchar(10), hr nvarchar(30),  dt int,att varchar(3))

And I inserted values as follows:

insert into stud values ('Abi',  '1',  '21/01/2013','a')
insert into stud values ('Abi',  '2',  '21/01/2013','p')


insert into stud values ('bala',  '1',  '21/01/2013','p')
insert into stud values ('bala',  '2',  '21/01/2013','a')


insert into stud values ('bala',  '1',  '22/01/2013','od')
insert into stud values ('bala',  '2',  '22/01/2013','ml')

insert into stud values ('Abi',  '1',  '22/01/2013','ml')
insert into stud values ('Abi',  '2',  '22/01/2013','od')

---

If select the table means means I got output as:

sname   hr   dt   att

abi      1    20/1/2013 a
abi      2    20/1/2013 p

bala      1    20/1/2013 p
bala      2    20/1/2013 a

---

But I want output as :

sname    h1    h2

abi      a      p
bala     p      a

How to pivot the table..

Answer 1

This type of transformation is known as a pivot. I am assuming SQL Server as the database due to the usage of nvarchar in the create table code you provided.

If you are using SQL Server 2005+, then you can use the pivot function and the code will be:

select sname, [1] as h1, [2] as h2
from
(
  select sname, hr, att
  from stud
  where att in ('a', 'p')
) d
pivot
(
  max(att)
  for hr in ([1], [2])
) p;

See SQL Fiddle with Demo.

If you are not using SQL Server 2005+ or are on another data base that does not support pivot, then you can use an aggregate function with a CASE expression:

select sname,
  max(case when hr=1 then att end) as H1,
  max(case when hr=2 then att end) as H2
from stud
where att in ('a', 'p')
group by sname;

See SQL Fiddle with Demo

Answer 2

The response by Ray in Sql Server 2008 Cross Tab Query should provide you with the details you need for a standards compliant DBMS.