日期:2014-05-17 浏览次数:20502 次
if OBJECT_ID('tempdb..#temp', 'u') is not null drop table #temp;
go
create table #temp( [职工号] varchar(100), [补贴A] INT, [补贴B] INT, [补贴C] INT, [补贴D] INT);
insert #temp
select '1','21',null,'25',null union all
select '2',null,'32','68','12' union all
select '3',null,null,'58','56' union all
select '4',null,'45',null,null
--SQL:
SELECT *
FROM
(
SELECT 职工号,xx,补贴类型=(CASE rowid WHEN 1 THEN '补贴A' WHEN 2 THEN '补贴B' WHEN 3 THEN '补贴C' WHEN 4 THEN '补贴D' END) FROM
(
select rowid=ROW_NUMBER() OVER(PARTITION BY 职工号 ORDER BY GETDATE()),职工号,xx
from #temp a
UNPIVOT
(xx FOR yy IN([补贴A],[补贴B],[补贴C],[补贴D])) b
) t
) m
PIVOT
(MAX(xx) FOR 补贴类型 IN([补贴A],[补贴B],[补贴C],[补贴D])) n