日期:2014-05-17 浏览次数:20556 次
WITH a1 (ID,A,B,C,D,E,F,G) AS
(
SELECT 123,1,1,1,1,NULL,NULL,NULL
)
,a2 AS
(
SELECT ID,'A' NAME,A VALUE FROM a1
UNION ALL
SELECT ID,'B',B FROM a1
UNION ALL
SELECT ID,'C',C FROM a1
UNION ALL
SELECT ID,'D',D FROM a1
UNION ALL
SELECT ID,'E',E FROM a1
UNION ALL
SELECT ID,'F',F FROM a1
UNION ALL
SELECT ID,'G',G FROM a1
)
SELECT * INTO #cu1 FROM a2 NAME WHERE VALUE IS NOT NULL
select distinct NAME into #cu2 from #cu1
declare @sql varchar(max),@sql2 varchar(max)
select @sql = isnull(@sql + '],[' , '') + NAME from #cu2
set @sql = '[' + @sql + ']'
select @sql2 = isnull(@sql2 + ',' , ',') + 'isnull(['+NAME+'],0) ['+NAME+']' from #cu2
set @sql='select ID'+@sql2+' from #cu1 a pivot (MAX(VALUE) for NAME in (' + @sql + ')) b order by ID'