SQL语句3维数据绘制报表，不知道如何写效率好，谢谢！

--> 测试数据: #tb

if object_id('tempdb.dbo.#tb') is not null drop table #tb

go 

create table #tb (款式 varchar(1),颜色 varchar(5),尺码 varchar(4),件数 int)

insert into #tb

select 'A','红','X',1 union all

select 'A','红','XL',2 union all

select 'A','红','XXL',3 union all

select 'A','绿','X',4 union all

select 'A','黄','XXL',5 union all

select 'A','黄','XLL',6 union all

select 'B','蓝','120M',7 union all

select 'B','蓝','130M',8 union all

select 'B','紫','120M',9 union all

select 'C','白','X',10 union all

select 'C','白','XL',11

 



SELECT 字段1='A',字段2='颜色',字段3='X',字段4='XL',字段5='XXL',字段6='XLL',字段7='合计' 

UNION ALL

select 款式,颜色=ISNULL(颜色,'合计'),

	 LTRIM(sum(case when 尺码='X' THEN 件数 else 0 end)),

	 LTRIM(sum(case when 尺码='XL' THEN 件数 else 0 end)),

	 LTRIM(sum(case when 尺码='XXL' THEN 件数 else 0 end)),

	 LTRIM(sum(case when 尺码='XLL' THEN 件数 else 0 end)),

	 LTRIM(sum(件数)) 

from #tb

where 款式='A'

GROUP BY 款式,颜色

WITH ROLLUP

HAVING(NOT (GROUPING(款式)=1 AND GROUPING(颜色)=1))

union all 

SELECT 'B','颜色','120M','130M','合计','',''

UNION ALL

select 款式,颜色=ISNULL(颜色,'合计'),

	 LTRIM(sum(case when 尺码='120M' THEN 件数 else 0 end)),

	 LTRIM(sum(case when 尺码='130M' THEN 件数 else 0 end)), 

	 LTRIM(sum(件数)),'',''

from #tb

where 款式='B'

GROUP BY 款式,颜色

WITH ROLLUP

HAVING(NOT (GROUPING(款式)=1 AND GROUPING(颜色)=1))

UNION ALL 

SELECT 'C','颜色','X','XL','合计','',''

UNION ALL 

select 款式,颜色 ,

	 LTRIM(sum(case when 尺码='X' THEN 件数 else 0 end)),

	 LTRIM(sum(case when 尺码='XL' THEN 件数 else 0 end)), 

	 LTRIM(sum(件数)),'',''

from #tb

where 款式='C'

GROUP BY 款式,颜色

 





字段1  字段2   字段3          字段4          字段5          字段6          字段7

---- ----- ------------ ------------ ------------ ------------ ------------

A    颜色    X            XL           XXL          XLL          合计

A    红     1            2            3            0            6

A    黄     0            0            5            6            11

A    绿     4            0            0            0            4

A    合计    5            2            8            6            21

B    颜色    120M         130M         合计                        

B    蓝     7            8            15                        

B    紫     9            0            9                         

B    合计    16           8            24                        

C    颜色    X            XL           合计                        

C    白     10           11           21                        



(11 行受影响)