标准动态行专列

来源：互联网发布：怎么改linux子目录权限编辑：程序博客网时间：2024/04/27 15:12

if OBJECT_ID('tab') is not null
    drop table tab
GO
create table tab([A] int ,[B] varchar(10), [C] varchar(10),[count] int)
insert tab
select 1,'产品1','不良1',12 union all
select 1,'产品2','不良3',23 union all
select 1,'产品1','不良2',5    union all
select 2,'产品3','不良1',9    union all
select 2,'产品4','不良5',30 union all
select 2,'产品3', null, 0 union all
select 2,'产品1', null, 0 union all
select 2,'产品4', null, 0 union all
select 2,'产品1', null, 0 union all
select 2,'产品2', null, 0 union all
select 2,'产品4','不良4',70

declare @sql nvarchar(4000)

set @sql='select a,b'

select @sql=@sql+', sum(case c when '''+isnull(c,'')+''' then [count] else 0 end) as '''+c+'''' from tab where c is not null group by c

set @sql=@sql+' from tab group by a,b'

exec(@sql)

----------- ---------- ----------- ----------- ----------- ----------- -----------
1           产品1        12          5           0           0           0
2           产品1        0           0           0           0           0
1           产品2        0           0           23          0           0
2           产品2        0           0           0           0           0
2           产品3        9           0           0           0           0
2           产品4        0           0           0           70          30

(6 行受影响)

a b 不良1 不良2 不良3 不良4 不良5