;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( 蠏碁9 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 蠏碁9 in ([A], [B], [C], [D])) as p1 ) select * from temp unpivot( for 蠏碁9 in ([A], [B], [C], [D])) as p1