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 Typeexec('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 Typeset @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)
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 Typeexec('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 Typeset @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)
UPING
显示结果需为
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
type name
T1 N1
T2 N2
T3 N3
T4 N4
希望显示的结果表的列名用name(n1,n2...),不用t1,t2..
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) aSELECT @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
) aSELECT @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
*/
我的意思表达错了
不是用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