;with pivot_t(id, yyyy, val)
as
(
select 1, 2001, 100 union all
select 2, 2002, 200 union all
select 3, 2003, 200 union all
select 1, 2004, 300 union all
select 2, 2005, 400 union all
select 3, 2006, 500
)
select *
from pivot_t pivot(sum(val) for yyyy in([2001], [2002], [2003])) as p1
join pivot_t pivot(avg(val) for yyyy in([2004], [2005], [2006])) as p2
on p1.id = p2.id
/*
id 2001 2002 2003 id 2004 2005 2006
----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 100 NULL NULL 1 300 NULL NULL
2 NULL 200 NULL 2 NULL 400 NULL
3 NULL NULL 200 3 NULL NULL 500
(3螳 レ 覦)
*/
--drop table #temp
create table #temp(
蠏碁９ varchar(20)
, int
)
insert #temp values
('A', 100)
, ('B', 200)
, ('C', 300)
, ('D', 400)
--pivot/unpivot
;with temp
as
(
select *
from #temp
pivot (sum() for 蠏碁９ in ([A], [B], [C], [D])) as p1
)
select *
from temp
unpivot( for 蠏碁９ in ([A], [B], [C], [D])) as p1