TYPE 状态
40A 好
40B 坏
40B 好
40C 好
40A 好
现在要显示成
40A 40B 40C . 。。。。。。
好 2 1 1
坏 0 1 0
ToTal 2 2 1
注意：TYPE中类型很多，只列了3种，如何横向列出type
------解决方案--------------------
- SQL code
--> 测试数据：[tb]if object_id('[tb]') is not null drop table [tb]gocreate table [tb]([TYPE] varchar(10),[状态] varchar(10))insert [tb]select '40A','好' union allselect '40B','坏' union allselect '40B','好' union allselect '40C','好' union allselect '40A','好'declare @s varchar(max)set @s=''select @[email protected]+','+quotename([TYPE])+'=sum(case when [TYPE]='+quotename([TYPE],'''')+' then 1 else 0 end)'from [tb] group by[TYPE]set @s = 'select isnull([状态],''ToTal'')[email protected]+' from [tb] group by [状态] with ROLLUP'exec(@s)/* 40A 40B 40C---------- ----------- ----------- -----------好 2 1 1坏 0 1 0ToTal 2 2 1*/
------解决方案--------------------
生成的語句格式如下：
- SQL code
select [状态]=isnull([状态],'ToTal'), [40A]=sum(case when [TYPE]='40A' then 1 else 0 end), [40B]=sum(case when [TYPE]='40B' then 1 else 0 end), [40C]=sum(case when [TYPE]='40C' then 1 else 0 end)from #T group by [状态] with rollup;
------解决方案--------------------
- SQL code
create table [tb]([TYPE] varchar(10),[状态] varchar(10))insert [tb]select '40A','好' union allselect '40B','好' union allselect '40B','好' union allselect '40C','好' union allselect '40A','好'declare @s varchar(max)=''select @[email protected]+','+quotename([TYPE])+'=sum(case when [TYPE]='+quotename([TYPE],'''')+' then 1 else 0 end)'from [tb] group by[TYPE]set @s = 'select isnull([状态],''ToTal'')[email protected]+' from (select * from [tb] union all select ''x'',''坏'' union all select ''x'',''好'') t group by [状态] with ROLLUP'exec(@s) 40A 40B 40C---------- ----------- ----------- -----------好 2 2 1坏 0 0 0ToTal 2 2 1(3 row(s) affected)