高手请进，高难度 行转列 问题

[ITpassport]

原有表s1
Item Type Qty
A T1 2
A T2 5
B T2 1
B T3 4
C T1 10
D T4 2
E 空格 0

需要的结果表s2如下
NAME T1 T2 T3 T4 T5 TOTAL
A 2 5 0 0 0 7
B 0 1 4 0 0 5
C 10 0 0 0 0 10
D 0 0 0 2 0 2
E 0 0 0 0 0 0
SUM 12 6 4 2 0 24

注意:type 有50个类型 ,name 有10000多个种类

[jinjazz]

--建立测试环境
Create Table 表(Item varchar(4),Type varchar(4),Qty integer)
--插入数据
insert into 表
select 'A','T1','2' union
select 'A','T2','5' union
select 'B','T2','1' union
select 'B','T3','4' union
select 'C','T1','10' union
select 'D','T4','2' union
select 'E','空格','0'
--select * from 表
--测试语句
DECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT Item'
SELECT @SQL= @SQL+ ',max(CASE WHEN Type = ''' + Type + ''' THEN Qty else 0 END) [' + Type + ']' FROM (SELECT DISTINCT Type FROM 表 union select '总计') A
SET @SQL=@SQL+' FROM (select * from 表 union select Item,''总计'',sum(Qty)Qty from 表 GROUP BY Item)a GROUP BY Item'
exec (@SQL)

--删除测试环境
Drop Table 表
/*
Item T1 T2 T3 T4 空格 总计
---- ----------- ----------- ----------- ----------- ----------- -----------
A 2 5 0 0 0 7
B 0 1 4 0 0 5
C 10 0 0 0 0 10
D 0 0 0 2 0 2
E 0 0 0 0 0 0
*/

[子陌红尘]

--生成测试数据
Create Table T(Item varchar(4),Type varchar(4),Qty integer)

insert into T select 'A','T1','2'
insert into T select 'A','T2','5'
insert into T select 'B','T2','1'
insert into T select 'B','T3','4'
insert into T select 'C','T1','10'
insert into T select 'D','T4','2'
insert into T select 'E','空格','0'

--测试语句
DECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT Item = isnull(Item,''SUM'')'

SELECT @SQL= @SQL+ ',max(CASE WHEN Type = ''' + Type + ''' THEN Qty else 0 END) [' + Type + ']' FROM (SELECT DISTINCT Type FROM T union select '总计') A

SET @SQL=@SQL+',TOTAL = SUM(Qty) FROM T GROUP BY Item WITH ROLLUP'
exec (@SQL)

[jinjazz]

--建立测试环境
Create Table 表(Item varchar(4),Type varchar(4),Qty integer)
--插入数据
insert into 表
select 'A','T1','2' union
select 'A','T2','5' union
select 'B','T2','1' union
select 'B','T3','4' union
select 'C','T1','10' union
select 'D','T4','2' union
select 'E','空格','0'
--select * from 表
--测试语句
DECLARE @SQL VARCHAR(8000),@SQL2 VARCHAR(8000)
SET @SQL='SELECT Item'
SELECT @SQL= @SQL+ ',max(CASE WHEN Type = ''' + Type + ''' THEN Qty else 0 END) [' + Type + ']' FROM (SELECT '0' o,Type FROM 表 union select '1', 'TOTAL' ) A
SET @SQL=@SQL+' FROM (select * from 表 union select Item,''TOTAL'',sum(Qty)Qty from 表 GROUP BY Item)a GROUP BY Item'
--exec (@SQL)
SET @SQL2=' union SELECT ''Sum''Item'
SELECT @SQL2= @SQL2+ ',sum(CASE WHEN Type = ''' + Type + ''' THEN Qty else 0 END) [' + Type + ']' FROM (SELECT DISTINCT Type FROM 表) A
SET @SQL2=@SQL2+',sum(Qty)TOTAL FROM 表'
exec (@SQL+@SQL2)
--删除测试环境
Drop Table 表
/*

Item T1 T2 T3 T4 空格 TOTAL
---- ----------- ----------- ----------- ----------- ----------- -----------
A 2 5 0 0 0 7
B 0 1 4 0 0 5
C 10 0 0 0 0 10
D 0 0 0 2 0 2
E 0 0 0 0 0 0
Sum 12 6 4 2 0 24
*/

[子陌红尘]

--建立测试环境
Create Table T(Item varchar(4),Type varchar(4),Qty integer)
--插入数据
insert into T
select 'A','T1','2' union
select 'A','T2','5' union
select 'B','T2','1' union
select 'B','T3','4' union
select 'C','T1','10' union
select 'D','T4','2' union
select 'E','空格','0'
--select * from T
--测试语句
DECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT Item = isnull(Item,''SUM'')'

SELECT @SQL= @SQL+ ',max(CASE WHEN Type = ''' + Type + ''' THEN Qty else 0 END) [' + Type + ']' FROM T A GROUP BY Type

SET @SQL=@SQL+',TOTAL = SUM(Qty) FROM T GROUP BY Item WITH ROLLUP'
exec (@SQL)

--删除测试环境
Drop Table T

[ITpassport]

我执行测试语句的时候，出现以下错误？？？
Server: Msg 1038, Level 15, State 3, Line 1
不能使用空白的对象或列名。如果必要，请使用一个空格。
Server: Msg 1038, Level 15, State 1, Line 1
不能使用空白的对象或列名。如果必要，请使用一个空格。
Server: Msg 170, Level 15, State 1, Line 1
第 1 行: 'a' 附近有语法错误。
Server: Msg 1038, Level 15, State 1, Line 1
不能使用空白的对象或列名。如果必要，请使用一个空格。
Server: Msg 1038, Level 15, State 1, Line 1
不能使用空白的对象或列名。如果必要，请使用一个空格。

[zjcxc]

--建立测试环境
Create Table T(Item varchar(4),Type varchar(4),Qty integer)
--插入数据
insert into T
select 'A','T1','2' union
select 'A','T2','5' union
select 'B','T2','1' union
select 'B','T3','4' union
select 'C','T1','10' union
select 'D','T4','2' union
select 'E',' ','0' union
select 'F',' ','10'
go

--查询处理
DECLARE @SQL VARCHAR(8000)
SET @SQL=''
SELECT @SQL= @SQL+ ','+quotename(Type)
+'=isnull(sum(CASE Type when '+quotename(type,'''')
+' THEN Qty END),0)'
FROM T A GROUP BY Type
exec('
select Item=case when grouping(Item)=1 then ''SUM'' else Item end'+@SQL+'
,TOTAL=sum(Qty)
from T
group by Item with rollup')
go

--删除测试环境
Drop Table T

/*--结果

Item T1 T2 T3 T4 TOTAL
---- ----------- ----------- ----------- ----------- ----------- -----------
A 0 2 5 0 0 7
B 0 0 1 4 0 5
C 0 10 0 0 0 10
D 0 0 0 0 2 2
E 0 0 0 0 0 0
F 10 0 0 0 0 10
SUM 10 12 6 4 2 34
--*/

[子陌红尘]

--建立测试环境
Create Table T(Item varchar(4),Type varchar(4),Qty integer)
--插入数据
insert into T
select 'A','T1','2' union
select 'A','T2','5' union
select 'B','T2','1' union
select 'B','T3','4' union
select 'C','T1','10' union
select 'D','T4','2' union
select 'E','空格','0'
--select * from T
--测试语句
DECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT Item = isnull(Item,''SUM'')'

SELECT @SQL= @SQL+ ',max(CASE WHEN Type = ''' + Type + ''' THEN Qty else 0 END) [' + Type + ']' FROM T GROUP BY Type

SET @SQL=@SQL+',TOTAL = SUM(Qty) FROM T GROUP BY Item WITH ROLLUP'
exec (@SQL)

--删除测试环境
Drop Table T

[ITpassport]

错误
错误
错误
错误
错误
错误
错误
错误


Server: Msg 1038, Level 15, State 3, Line 1
不能使用空白的对象或列名。如果必要，请使用一个空格。
Server: Msg 1038, Level 15, State 1, Line 1
不能使用空白的对象或列名。如果必要，请使用一个空格。

[zjcxc]

我的可以啊

[zjcxc]

除非你给的数据中的空格,你并没有打上去,否则是没有问题的

[zjcxc]

--如果你确实忘记了打空格上去,则改为:


--建立测试环境
Create Table T(Item varchar(4),Type varchar(4),Qty integer)
--插入数据
insert into T
select 'A','T1','2' union
select 'A','T2','5' union
select 'B','T2','1' union
select 'B','T3','4' union
select 'C','T1','10' union
select 'D','T4','2' union
select 'E','','0' union
select 'F','','10'
go

--查询处理
DECLARE @SQL VARCHAR(8000)
SET @SQL=''
SELECT @SQL= @SQL+ ','+quotename(case when Type='' then ' ' else Type end)
+'=isnull(sum(CASE Type when '+quotename(type,'''')
+' THEN Qty END),0)'
FROM T A GROUP BY Type
exec('
select Item=case when grouping(Item)=1 then ''SUM'' else Item end'+@SQL+'
,TOTAL=sum(Qty)
from T
group by Item with rollup')
go

--删除测试环境
Drop Table T

/*--结果

Item T1 T2 T3 T4 TOTAL
---- ----------- ----------- ----------- ----------- ----------- -----------
A 0 2 5 0 0 7
B 0 0 1 4 0 5
C 0 10 0 0 0 10
D 0 0 0 0 2 2
E 0 0 0 0 0 0
F 10 0 0 0 0 10
SUM 10 12 6 4 2 34
--*/

[zhangyanxxxx]

create table #a(item VARchar(2),type VARchar(4),qty int)
insert into #a values('A','T1',2)
insert into #a values('A','T2',5)
insert into #a values('B','T2',4)
insert into #a values('B','T3',4)
insert into #a values('C','T1',10)
insert into #a values('D','T4',2)
insert into #a values('E','空格',0)


DECLARE @STR VARCHAR(8000)
DECLARE @STR1 VARCHAR(8000)
DECLARE @STR2 VARCHAR(8000)
DECLARE @TYPE VARCHAR(3)
DECLARE @SUM CHAR(4)
DECLARE TYPECUR CURSOR FOR SELECT DISTINCT TYPE FROM #A
SET @STR=''
SET @STR1=''
SET @STR2=''
SET @SUM='SUM'
OPEN TYPECUR
FETCH NEXT FROM TYPECUR INTO @TYPE
WHILE @@FETCH_STATUS = 0
BEGIN
SET @STR=@STR+'SUM(CASE TYPE WHEN '+ ''''+@TYPE+''''+' THEN QTY ELSE 0 END) AS ' +@TYPE+','
SET @STR1=@STR1+'SUM(CASE TYPE WHEN '+ ''''+@TYPE+''''+' THEN QTY ELSE 0 END)' +','
FETCH NEXT FROM TYPECUR INTO @TYPE
END
CLOSE TYPECUR
DEALLOCATE TYPECUR
SET @STR2=@STR
SET @STR='SELECT ITEM,'+ @STR+LEFT(REPLACE(@STR1,',','+'),LEN(@STR1)-1)+' AS TOTAL FROM #A GROUP BY ITEM'
SET @STR1='SELECT '+''''+@SUM+''','+ @STR2+LEFT(REPLACE(@STR1,',','+'),LEN(@STR1)-1)+' AS TOTAL FROM #A '
SET @STR=@STR+' UNION ALL '+@STR1
EXEC(@STR)