SQL数量统计有关问题，请问

SQL数量统计问题，请教
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 @s=@s+','+quotename([TYPE])+'=sum(case when [TYPE]='+quotename([TYPE],'''')+' then 1 else 0 end)'from [tb] group by[TYPE]set @s = 'select isnull([状态],''ToTal'')'+@s+'  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 @s=@s+','+quotename([TYPE])+'=sum(case when [TYPE]='+quotename([TYPE],'''')+' then 1 else 0 end)'from [tb] group by[TYPE]set @s = 'select isnull([状态],''ToTal'')'+@s+' 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)