sql PIVOT,UNPIVOT,GROUPING SETS

if OBJECT_ID('Orders')is not null
drop table Orders
go
create table Orders
(
orderid int not null,
orderdate date not null,
empid int not null,
cusid varchar(5)not null,
qty int not null,
constraint PK_Order primary key(orderid)
)
insert into Orders
(
orderid,orderdate,empid,cusid,qty
)
select 30001,'20070802',3,'A',10
union all
select 10001,'20071224',2,'A',12
union all
select 10005,'20071224',1,'B',20
union all 
select 40001,'20080109',2,'A',40
union all 
select 10006,'20080118',1,'C',14
union all
select 20001,'20080212',2,'B',12
union all 
select 40005,'20080212',3,'A',10
union all 
select 20002,'20080216',1,'C',20
union all
select 20003,'20090418',2,'B',15
union all
select 30004,'20070418',3,'C',
22
union all
select 30007,'20090907',3,'D',30
go
--sql2000Staticsql
select empid,
		SUM(case when cusid='A'then qty else 0 end)'A',
		SUM(case when cusid='B'then qty else 0 end)'B',
		SUM(case when cusid='C'then qty else 0 end)'C',
		SUM(case when cusid='D'then qty else 0 end)'D'
from
Orders group by empid
--sql2000Dynamicsql

declare @sql varchar(2000)
set @sql='select empid'
select @sql=@sql+',sum(case when cusid='''+O.cusid+''' then qty else 0 end)['+O.cusid+']'
from(select distinct cusid from Orders)O
set @sql=@sql+'from Orders group by empid'
exec(@sql)
--PIVOTMethod
select empid,A,B,C,D
--intoCusOrders
from
(
select empid,cusid,qty from Orders
)
sourceOrder
pivot
(
sum(qty)
for cusid in(A,B,C,D)
)
as
targetOrder
--UNPIVOTMethod
select
empid,cusid,qty from CusOrders
unpivot
(
qty for cusid in(A,B,C,D)
)
as[Target]
--Groupingsets
select empid,cusid,SUM(qty) qty
from Orders
group by Grouping
sets
(
(empid,cusid),
(empid),
(cusid),
()
)
--cube

--CubeisequivalenttoGROUPINGSets

select empid,cusid,SUM(qty) qty 
from Orders
group by cube(empid,cusid)