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

[ITpassport]

--建立测试环境
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

问题1：可否直接去除第二列（列名为空的那列）
问题2：现有表TYPENAME
type name
T1 N1
T2 N2
T3 N3
T4 N4
希望以上显示的结果表的列名用name(n1,n2...),不用t1,t2..
问题3：
原有表s1改成(增加列PKG)
Item Type Qty PKG
A T1 2 P1
A T2 5 P1
B T2 1 P1
B T3 4 P1
C T1 10 P2
D T4 2 P2
E '' 0 P2
显示结果需为
PKG NAME T1 T2 T3 T4 T5 TOTAL
P1 A 2 5 0 0 0 7
P1 B 0 1 4 0 0 5
P1 C 10 0 0 0 0 10
SUM 12 6 4 0 0 22
P2 D 0 0 0 2 0 2
P2 E 0 0 0 0 0 0
SUM 0 0 0 2 0 2
TOTAL 12 6 4 2 0 24

[子陌红尘]

create table t(
Item char(1),
Type char(2),
Qty int,
PKG char(2))

insert into t select 'A','T1',2 ,'P1'
insert into t select 'A','T2',5 ,'P1'
insert into t select 'B','T2',1 ,'P1'
insert into t select 'B','T3',4 ,'P1'
insert into t select 'C','T1',10,'P2'
insert into t select 'D','T4',2 ,'P2'
insert into t select 'E','' ,0 ,'P2'



DECLARE @SQL VARCHAR(8000)
SET @SQL=''

SELECT identity(int,1,1) as ID,a.Type INTO #t from (select distinct Type from t where Rtrim(Type) != '' and Type is not null) a

SELECT @SQL= @SQL+ ','+'N'+RTRIM(ID)+'=isnull(sum(CASE Type when '+quotename(type,'''')+' THEN Qty END),0)'
FROM #T A

set @SQL = 'select case when PKG is null and Item is null then ''TOTAL''
when PKG is not null and Item is null then ''SUM''
else PKG
end,
Item'+@SQL+',TOTAL=sum(Qty) from T group by PKG,Item with rollup'

exec(@SQL)

[子陌红尘]

--生成测试数据
create table t(
Item char(1),
Type char(2),
Qty int,
PKG char(2))

insert into t select 'A','T1',2 ,'P1'
insert into t select 'A','T2',5 ,'P1'
insert into t select 'B','T2',1 ,'P1'
insert into t select 'B','T3',4 ,'P1'
insert into t select 'C','T1',10,'P2'
insert into t select 'D','T4',2 ,'P2'
insert into t select 'E','' ,0 ,'P2'


--执行查询
DECLARE @SQL VARCHAR(8000)
SET @SQL=''

SELECT
identity(int,1,1) as ID,a.Type
INTO #t
FROM
(select distinct Type from t
--注：如果不需要过滤Type为空或''的记录，则删除如下条件
where Rtrim(Type) != '' and Type is not null
) a

SELECT @SQL= @SQL+ ','+'N'+RTRIM(ID)+
'=isnull(sum(CASE Type when '+quotename(type,'''')+' THEN Qty END),0)'
FROM #T A

set @SQL = 'select case when PKG is null and Item is null then ''TOTAL''
when PKG is not null and Item is null then ''SUM''
else PKG
end as PKG,
Item'+@SQL+',TOTAL=sum(Qty) from T group by PKG,Item with rollup'

exec(@SQL)


--删除测试数据
drop table t
drop table #t


--执行结果
/*
PKG NAME N1 N2 N3 N4 TOTAL
-----------------------------------------------------
P1 A 2 5 0 0 7
P1 B 0 1 4 0 5
SUM NULL 2 6 4 0 12
P2 C 10 0 0 0 10
P2 D 0 0 0 2 2
P2 E 0 0 0 0 0
SUM NULL 10 0 0 2 12
TOTAL NULL 12 6 4 2 24
*/

[ITpassport]

libin_ftsafe(子陌红尘)
我的意思表达错了
不是用N+ID，是要根据type name来命名列
type name
T1 AAA
T2 ERRORT
T3 F344
T4 SOMET
所以+'N'+RTRIM(ID) 有问题

[子陌红尘]

--生成测试数据
create table t(
Item char(1),
Type char(2),
Qty int,
PKG char(2))

insert into t select 'A','T1',2 ,'P1'
insert into t select 'A','T2',5 ,'P1'
insert into t select 'B','T2',1 ,'P1'
insert into t select 'B','T3',4 ,'P1'
insert into t select 'C','T1',10,'P2'
insert into t select 'D','T4',2 ,'P2'
insert into t select 'E','' ,0 ,'P2'

CREATE TABLE TYPENAME(
Type char(2),
Name char(2))

insert into typename select 'T1','N1'
insert into typename select 'T2','N2'
insert into typename select 'T3','N3'
insert into typename select 'T4','N4'
insert into typename select '' ,'N5'

--执行查询
DECLARE @SQL VARCHAR(8000)
SET @SQL=''


SELECT @SQL= @SQL+ ','+quotename(B.name,'''')+
'=isnull(sum(CASE Type when '+quotename(A.type,'''')+' THEN Qty END),0)'
FROM T A,TYPENAME B
WHERE
A.type = B.type

set @SQL = 'select case when PKG is null and Item is null then ''TOTAL''
when PKG is not null and Item is null then ''SUM''
else PKG
end as PKG,
Item'+@SQL+',TOTAL=sum(Qty) from T group by PKG,Item with rollup'

exec(@SQL)


--删除测试数据
drop table t
drop table TYPENAME

[duanhai]

強

我原來一直沒有用過rollup

想不到還有這種功效，收藏了

[子陌红尘]

1、

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 where Rrim(Type) != '' and Type is not null 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')

2、

create table t(
Item char(1),
Type char(2),
Qty int,
PKG char(2))

insert into t select 'A','T1',2 ,'P1'
insert into t select 'A','T2',5 ,'P1'
insert into t select 'B','T2',1 ,'P1'
insert into t select 'B','T3',4 ,'P1'
insert into t select 'C','T1',10,'P2'
insert into t select 'D','T4',2 ,'P2'
insert into t select 'E','' ,0 ,'P2'


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 where Rtrim(Type) != '' and Type is not null GROUP BY Type

set @SQL = 'select case when PKG is null and Item is null then ''TOTAL''
when PKG is not null and Item is null then ''SUM''
else PKG
end,
Item'+@SQL+',TOTAL=sum(Qty) from T group by PKG,Item with rollup'

exec(@SQL)

[worktest]

看这么长，就知道高难度了
UPING

[ITpassport]

补充
显示结果需为
PKG NAME N1 N2 N3 N4 N5 TOTAL
P1 A 2 5 0 0 0 7
P1 B 0 1 4 0 0 5
P1 C 10 0 0 0 0 10
SUM 12 6 4 0 0 22
P2 D 0 0 0 2 0 2
P2 E 0 0 0 0 0 0
SUM 0 0 0 2 0 2
TOTAL 12 6 4 2 0 24

[ITpassport]

问题2：现有表TYPENAME
type name
T1 N1
T2 N2
T3 N3
T4 N4
希望显示的结果表的列名用name(n1,n2...),不用t1,t2..

[sindia]

UP