不好意思,问题原始表修改一下单据日期 品种分类 品种编号 品种名称 供应商 数量
2008-10-1 a类 1001 A产品 甲 100
2008-10-1 b类 1002 B产品 乙 200
2008-10-1 C类 1003 C产品 丙 300
2008-10-1 d类 1004 d产品 丁 400
2008-11-1 a类 1001 A产品 甲 100
2008-11-1 a类 1001-1 A1产品 甲 100
2008-11-1 b类 1002 B产品 乙 200
2008-11-1 C类 1003 C产品 丙 300
2008-11-1 d类 1004 d产品 丁 400
2008-12-1 a类 1001 A产品 甲 100
2008-12-1 b类 1002 B产品 乙 200
2008-12-1 C类 1003 C产品 丙 300
2008-12-1 d类 1004 d产品 丁 400
2008-10-1 a类 1001 A产品 甲 100
2008-10-1 b类 1002 B产品 乙 200
2008-10-1 C类 1003 C产品 丙 300
2008-10-1 d类 1004 d产品 丁 400
2008-11-1 a类 1001 A产品 甲 100
2008-11-1 a类 1001-1 A1产品 甲 100
2008-11-1 b类 1002 B产品 乙 200
2008-11-1 C类 1003 C产品 丙 300
2008-11-1 d类 1004 d产品 丁 400
2008-12-1 a类 1001 A产品 甲 100
2008-12-1 b类 1002 B产品 乙 200
2008-12-1 C类 1003 C产品 丙 300
2008-12-1 d类 1004 d产品 丁 400
参考:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果):
姓名 语文 数学 物理
---- ---- ---- ----
李四 74 84 94
张三 74 83 93
-------------------
*/create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
go--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)
select 姓名 as 姓名 ,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql) --SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by 课程
set @sql = '[' + @sql + ']'
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')
insert into tb values('2008-10-1', 'a类' , '1001' , 'A产品' , '甲' , 100 )
insert into tb values('2008-10-1', 'b类' , '1002' , 'B产品' , '乙' , 200 )
insert into tb values('2008-10-1', 'C类' , '1003' , 'C产品' , '丙' , 300 )
insert into tb values('2008-10-1', 'd类' , '1004' , 'd产品' , '丁' , 400 )
insert into tb values('2008-11-1', 'a类' , '1001' , 'A产品' , '甲' , 100 )
insert into tb values('2008-11-1', 'a类' , '1001' , 'A1产品', '甲' , 100 )
insert into tb values('2008-11-1', 'b类' , '1002' , 'B产品' , '乙' , 200 )
insert into tb values('2008-11-1', 'C类' , '1003' , 'C产品' , '丙' , 300 )
insert into tb values('2008-11-1', 'd类' , '1004' , 'd产品' , '丁' , 400 )
insert into tb values('2008-12-1', 'a类' , '1001' , 'A产品' , '甲' , 100 )
insert into tb values('2008-12-1', 'b类' , '1002' , 'B产品' , '乙' , 200 )
insert into tb values('2008-12-1', 'C类' , '1003' , 'C产品' , '丙' , 300 )
insert into tb values('2008-12-1', 'd类' , '1004' , 'd产品' , '丁' , 400 )
goselect 品种分类,
sum(case when convert(varchar(7) , 单据日期,120)=convert(varchar(7),getdate(),120) then 数量 else 0 end) 合计_本月,
sum(case when year(单据日期)=year(getdate()) then 数量 else 0 end) 合计_本年,
sum(case when 供应商 = '甲' and convert(varchar(7) , 单据日期,120)=convert(varchar(7),getdate(),120) then 数量 else 0 end) 甲_本月,
sum(case when 供应商 = '甲' and year(单据日期)=year(getdate()) then 数量 else 0 end) 甲_本年,
sum(case when 供应商 = '乙' and convert(varchar(7) , 单据日期,120)=convert(varchar(7),getdate(),120) then 数量 else 0 end) 乙_本月,
sum(case when 供应商 = '乙' and year(单据日期)=year(getdate()) then 数量 else 0 end) 乙_本年,
sum(case when 供应商 = '丙' and convert(varchar(7) , 单据日期,120)=convert(varchar(7),getdate(),120) then 数量 else 0 end) 丙_本月,
sum(case when 供应商 = '丙' and year(单据日期)=year(getdate()) then 数量 else 0 end) 丙_本年,
sum(case when 供应商 = '丁' and convert(varchar(7) , 单据日期,120)=convert(varchar(7),getdate(),120) then 数量 else 0 end) 丁_本月,
sum(case when 供应商 = '丁' and year(单据日期)=year(getdate()) then 数量 else 0 end) 丁_本年
from tb
group by 品种分类
union all
select 品种名称,
sum(case when convert(varchar(7) , 单据日期,120)=convert(varchar(7),getdate(),120) then 数量 else 0 end) 合计_本月,
sum(case when year(单据日期)=year(getdate()) then 数量 else 0 end) 合计_本年,
sum(case when 供应商 = '甲' and convert(varchar(7) , 单据日期,120)=convert(varchar(7),getdate(),120) then 数量 else 0 end) 甲_本月,
sum(case when 供应商 = '甲' and year(单据日期)=year(getdate()) then 数量 else 0 end) 甲_本年,
sum(case when 供应商 = '乙' and convert(varchar(7) , 单据日期,120)=convert(varchar(7),getdate(),120) then 数量 else 0 end) 乙_本月,
sum(case when 供应商 = '乙' and year(单据日期)=year(getdate()) then 数量 else 0 end) 乙_本年,
sum(case when 供应商 = '丙' and convert(varchar(7) , 单据日期,120)=convert(varchar(7),getdate(),120) then 数量 else 0 end) 丙_本月,
sum(case when 供应商 = '丙' and year(单据日期)=year(getdate()) then 数量 else 0 end) 丙_本年,
sum(case when 供应商 = '丁' and convert(varchar(7) , 单据日期,120)=convert(varchar(7),getdate(),120) then 数量 else 0 end) 丁_本月,
sum(case when 供应商 = '丁' and year(单据日期)=year(getdate()) then 数量 else 0 end) 丁_本年
from tb
group by 品种名称
order by 品种分类drop table tb/*
品种分类 合计_本月 合计_本年 甲_本月 甲_本年 乙_本月 乙_本年 丙_本月 丙_本年 丁_本月 丁_本年
---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
A1产品 0 100 0 100 0 0 0 0 0 0
A产品 100 300 100 300 0 0 0 0 0 0
a类 100 400 100 400 0 0 0 0 0 0
B产品 200 600 0 0 200 600 0 0 0 0
b类 200 600 0 0 200 600 0 0 0 0
C产品 300 900 0 0 0 0 300 900 0 0
C类 300 900 0 0 0 0 300 900 0 0
d产品 400 1200 0 0 0 0 0 0 400 1200
d类 400 1200 0 0 0 0 0 0 400 1200(所影响的行数为 9 行)
*/
insert into tb values('2008-10-1', 'a类' , '1001' , 'A产品' , '甲' , 100 )
insert into tb values('2008-10-1', 'b类' , '1002' , 'B产品' , '乙' , 200 )
insert into tb values('2008-10-1', 'C类' , '1003' , 'C产品' , '丙' , 300 )
insert into tb values('2008-10-1', 'd类' , '1004' , 'd产品' , '丁' , 400 )
insert into tb values('2008-11-1', 'a类' , '1001' , 'A产品' , '甲' , 100 )
insert into tb values('2008-11-1', 'a类' , '1001' , 'A1产品', '甲' , 100 )
insert into tb values('2008-11-1', 'b类' , '1002' , 'B产品' , '乙' , 200 )
insert into tb values('2008-11-1', 'C类' , '1003' , 'C产品' , '丙' , 300 )
insert into tb values('2008-11-1', 'd类' , '1004' , 'd产品' , '丁' , 400 )
insert into tb values('2008-12-1', 'a类' , '1001' , 'A产品' , '甲' , 100 )
insert into tb values('2008-12-1', 'b类' , '1002' , 'B产品' , '乙' , 200 )
insert into tb values('2008-12-1', 'C类' , '1003' , 'C产品' , '丙' , 300 )
insert into tb values('2008-12-1', 'd类' , '1004' , 'd产品' , '丁' , 400 )
go--SQL2000静态
select 品种分类,
sum(case when convert(varchar(7) , 单据日期,120)=convert(varchar(7),getdate(),120) then 数量 else 0 end) 合计_本月,
sum(case when year(单据日期)=year(getdate()) then 数量 else 0 end) 合计_本年,
sum(case when 供应商 = '甲' and convert(varchar(7) , 单据日期,120)=convert(varchar(7),getdate(),120) then 数量 else 0 end) 甲_本月,
sum(case when 供应商 = '甲' and year(单据日期)=year(getdate()) then 数量 else 0 end) 甲_本年,
sum(case when 供应商 = '乙' and convert(varchar(7) , 单据日期,120)=convert(varchar(7),getdate(),120) then 数量 else 0 end) 乙_本月,
sum(case when 供应商 = '乙' and year(单据日期)=year(getdate()) then 数量 else 0 end) 乙_本年,
sum(case when 供应商 = '丙' and convert(varchar(7) , 单据日期,120)=convert(varchar(7),getdate(),120) then 数量 else 0 end) 丙_本月,
sum(case when 供应商 = '丙' and year(单据日期)=year(getdate()) then 数量 else 0 end) 丙_本年,
sum(case when 供应商 = '丁' and convert(varchar(7) , 单据日期,120)=convert(varchar(7),getdate(),120) then 数量 else 0 end) 丁_本月,
sum(case when 供应商 = '丁' and year(单据日期)=year(getdate()) then 数量 else 0 end) 丁_本年
from tb
group by 品种分类
union all
select 品种名称,
sum(case when convert(varchar(7) , 单据日期,120)=convert(varchar(7),getdate(),120) then 数量 else 0 end) 合计_本月,
sum(case when year(单据日期)=year(getdate()) then 数量 else 0 end) 合计_本年,
sum(case when 供应商 = '甲' and convert(varchar(7) , 单据日期,120)=convert(varchar(7),getdate(),120) then 数量 else 0 end) 甲_本月,
sum(case when 供应商 = '甲' and year(单据日期)=year(getdate()) then 数量 else 0 end) 甲_本年,
sum(case when 供应商 = '乙' and convert(varchar(7) , 单据日期,120)=convert(varchar(7),getdate(),120) then 数量 else 0 end) 乙_本月,
sum(case when 供应商 = '乙' and year(单据日期)=year(getdate()) then 数量 else 0 end) 乙_本年,
sum(case when 供应商 = '丙' and convert(varchar(7) , 单据日期,120)=convert(varchar(7),getdate(),120) then 数量 else 0 end) 丙_本月,
sum(case when 供应商 = '丙' and year(单据日期)=year(getdate()) then 数量 else 0 end) 丙_本年,
sum(case when 供应商 = '丁' and convert(varchar(7) , 单据日期,120)=convert(varchar(7),getdate(),120) then 数量 else 0 end) 丁_本月,
sum(case when 供应商 = '丁' and year(单据日期)=year(getdate()) then 数量 else 0 end) 丁_本年
from tb
group by 品种名称
order by 品种分类
/*
品种分类 合计_本月 合计_本年 甲_本月 甲_本年 乙_本月 乙_本年 丙_本月 丙_本年 丁_本月 丁_本年
---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
A1产品 0 100 0 100 0 0 0 0 0 0
A产品 100 300 100 300 0 0 0 0 0 0
a类 100 400 100 400 0 0 0 0 0 0
B产品 200 600 0 0 200 600 0 0 0 0
b类 200 600 0 0 200 600 0 0 0 0
C产品 300 900 0 0 0 0 300 900 0 0
C类 300 900 0 0 0 0 300 900 0 0
d产品 400 1200 0 0 0 0 0 0 400 1200
d类 400 1200 0 0 0 0 0 0 400 1200(所影响的行数为 9 行)
*/--SQL2000动态
declare @sql1 varchar(8000)
set @sql1 = 'select 品种分类,sum(case when convert(varchar(7) , 单据日期,120)=convert(varchar(7),getdate(),120) then 数量 else 0 end) 合计_本月,
sum(case when year(单据日期)=year(getdate()) then 数量 else 0 end) 合计_本年 '
select @sql1 = @sql1 + ' , sum(case when 供应商 = ''' + 供应商 + ''' and convert(varchar(7) , 单据日期,120)=convert(varchar(7),getdate(),120) then 数量 else 0 end) [' + 供应商 + '_本月]'
+ ' , sum(case when 供应商 = ''' + 供应商 + ''' and year(单据日期)=year(getdate()) then 数量 else 0 end) [' + 供应商 + '_本年]'
from (select distinct 供应商 from tb) as a
set @sql1 = @sql1 + ' from tb group by 品种分类 '
declare @sql2 varchar(8000)
set @sql2 = 'select 品种名称,sum(case when convert(varchar(7) , 单据日期,120)=convert(varchar(7),getdate(),120) then 数量 else 0 end) 合计_本月,
sum(case when year(单据日期)=year(getdate()) then 数量 else 0 end) 合计_本年 '
select @sql2 = @sql2 + ' , sum(case when 供应商 = ''' + 供应商 + ''' and convert(varchar(7) , 单据日期,120)=convert(varchar(7),getdate(),120) then 数量 else 0 end) [' + 供应商 + '_本月]'
+ ' , sum(case when 供应商 = ''' + 供应商 + ''' and year(单据日期)=year(getdate()) then 数量 else 0 end) [' + 供应商 + '_本年]'
from (select distinct 供应商 from tb) as a
set @sql2 = @sql2 + ' from tb group by 品种名称 'exec(@sql1 + ' union all ' + @sql2 + ' order by 品种分类')
/*
品种分类 合计_本月 合计_本年 丙_本月 丙_本年 丁_本月 丁_本年 甲_本月 甲_本年 乙_本月 乙_本年
---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
A1产品 0 100 0 0 0 0 0 100 0 0
A产品 100 300 0 0 0 0 100 300 0 0
a类 100 400 0 0 0 0 100 400 0 0
B产品 200 600 0 0 0 0 0 0 200 600
b类 200 600 0 0 0 0 0 0 200 600
C产品 300 900 300 900 0 0 0 0 0 0
C类 300 900 300 900 0 0 0 0 0 0
d产品 400 1200 0 0 400 1200 0 0 0 0
d类 400 1200 0 0 400 1200 0 0 0 0*/drop table tb
CREATE TABLE TEMP
(
单据日期 DATETIME
,品种分类 NVARCHAR(10)
,品种编号 NVARCHAR(10)
,品种名称 NVARCHAR(10)
,供应商 NVARCHAR(10)
,数量 INT
)
GOINSERT INTO TEMP
SELECT'2008-10-1','a类','1001', 'A产品', '甲','100' UNION ALL
SELECT'2008-10-1','b类','1002', 'B产品', '乙','200' UNION ALL
SELECT'2008-10-1','C类','1003', 'C产品', '丙','300' UNION ALL
SELECT'2008-10-1','d类','1004', 'd产品', '丁','400' UNION ALL
SELECT'2008-11-1','a类','1001', 'A产品', '甲','100' UNION ALL
SELECT'2008-11-1','a类','1001-1','A1产品','甲','100' UNION ALL
SELECT'2008-11-1','b类','1002', 'B产品', '乙','200' UNION ALL
SELECT'2008-11-1','C类','1003', 'C产品', '丙','300' UNION ALL
SELECT'2008-11-1','d类','1004', 'd产品', '丁','400' UNION ALL
SELECT'2008-12-1','a类','1001', 'A产品', '甲','100' UNION ALL
SELECT'2008-12-1','b类','1002', 'B产品', '乙','200' UNION ALL
SELECT'2008-12-1','C类','1003', 'C产品', '丙','300' UNION ALL
SELECT'2008-12-1','d类','1004', 'd产品', '丁','400'
GO-- 固定列 SELECT CASE WHEN 品种名称 IS NULL AND 品种分类 IS NOT NULL THEN 品种分类
WHEN 品种名称 IS NULL AND 品种分类 IS NULL THEN '总计'
ELSE 品种名称 END 品种
,SUM(本月数量) AS 本月合计
,SUM(本年数量) AS 本年合计
,SUM(CASE WHEN 供应商 = '甲' THEN 本月数量 ELSE 0 END) AS [本月甲]
,SUM(CASE WHEN 供应商 = '甲' THEN 本年数量 ELSE 0 END) AS [本年甲]
,SUM(CASE WHEN 供应商 = '乙' THEN 本月数量 ELSE 0 END) AS [本月乙]
,SUM(CASE WHEN 供应商 = '乙' THEN 本年数量 ELSE 0 END) AS [本年乙]
,SUM(CASE WHEN 供应商 = '丙' THEN 本月数量 ELSE 0 END) AS [本月丙]
,SUM(CASE WHEN 供应商 = '丙' THEN 本年数量 ELSE 0 END) AS [本年丙]
,SUM(CASE WHEN 供应商 = '丁' THEN 本月数量 ELSE 0 END) AS [本月丁]
,SUM(CASE WHEN 供应商 = '丁' THEN 本年数量 ELSE 0 END) AS [本年丁]
FROM (
SELECT 品种分类
,品种名称
,供应商
,SUM(CASE WHEN CONVERT(VARCHAR(4),单据日期,112)=CONVERT(VARCHAR(4),GETDATE(),112) THEN 数量 ELSE 0 END) AS 本年数量
,SUM(CASE WHEN CONVERT(VARCHAR(6),单据日期,112)=CONVERT(VARCHAR(6),GETDATE(),112) THEN 数量 ELSE 0 END) AS 本月数量
FROM TEMP
GROUP BY 品种分类,品种名称,供应商
) X
GROUP BY 品种分类,品种名称
WITH ROLLUP
ORDER BY CASE WHEN 品种名称 IS NULL AND 品种分类 IS NULL THEN 1 ELSE 0 END,品种分类,CASE WHEN 品种名称 IS NOT NULL THEN 1 ELSE 0 END
--不固定列,用动态SQL
DECLARE @sql NVARCHAR(4000)
SET @sql = '
SELECT CASE WHEN 品种名称 IS NULL AND 品种分类 IS NOT NULL THEN 品种分类
WHEN 品种名称 IS NULL AND 品种分类 IS NULL THEN ''总计''
ELSE 品种名称 END 品种
,SUM(本月数量) AS 本月合计
,SUM(本年数量) AS 本年合计 '
SELECT @sql = @sql + ',SUM(CASE WHEN 供应商 = ''' + 供应商 + ''' THEN 本月数量 ELSE 0 END) AS [本月' + 供应商 + ']
,SUM(CASE WHEN 供应商 = ''' + 供应商 + ''' THEN 本年数量 ELSE 0 END) AS [本年' + 供应商 + ']'
FROM (SELECT DISTINCT 供应商 FROM TEMP )AS A
SET @sql = @sql + ' FROM (SELECT 品种分类
,品种名称
,供应商
,SUM(CASE WHEN CONVERT(VARCHAR(4),单据日期,112)=CONVERT(VARCHAR(4),GETDATE(),112) THEN 数量 ELSE 0 END) AS 本年数量
,SUM(CASE WHEN CONVERT(VARCHAR(6),单据日期,112)=CONVERT(VARCHAR(6),GETDATE(),112) THEN 数量 ELSE 0 END) AS 本月数量
FROM TEMP
GROUP BY 品种分类,品种名称,供应商
) xx GROUP BY 品种分类,品种名称 WITH ROLLUP
'EXEC(@sql)
declare @t table (单据日期 datetime, 品种分类 varchar(10),品种编号 varchar(10),品种名称 varchar(10),供应商 varchar(10),数量 int)
insert @t
select '2008-10-1','a类','1001','A产品','甲',100 union
select '2008-10-1','b类','1002','B产品','乙',200 union
select '2008-10-1','C类','1003','C产品','丙',300 union
select '2008-10-1','d类','1004','d产品','丁',400 union
select '2008-11-1','a类','1001','A产品','甲',100 union
select '2008-11-1','a类','1001-1','A1产品','甲',100 union
select '2008-11-1','b类','1002','B产品','乙',200 union
select '2008-11-1','C类','1003','C产品','丙',300 union
select '2008-11-1','d类','1004','d产品','丁',400 union
select '2008-12-1','a类','1001','A产品','甲',100 union
select '2008-12-1','b类','1002','B产品','乙',200 union
select '2008-12-1','C类','1003','C产品','丙',300 union
select '2008-12-1','d类','1004','d产品','丁',400 --查询
select 品种分类=isnull(isnull(品种名称,品种分类),'合计'),
本月合计=sum(case when datediff(month,单据日期,'2008-11-1')=0 then 数量 else 0 end),
本年合计=sum(数量),
甲本月=sum(case when 供应商='甲' and datediff(month,单据日期,'2008-11-1')=0 then 数量 else 0 end),
甲本年=sum(case when 供应商='甲' then 数量 else 0 end),
乙本月=sum(case when 供应商='乙' and datediff(month,单据日期,'2008-11-1')=0 then 数量 else 0 end),
乙本年=sum(case when 供应商='乙' then 数量 else 0 end),
丙本月=sum(case when 供应商='丙' and datediff(month,单据日期,'2008-11-1')=0 then 数量 else 0 end),
丙本年=sum(case when 供应商='丙' then 数量 else 0 end),
丁本月=sum(case when 供应商='丁' and datediff(month,单据日期,'2008-11-1')=0 then 数量 else 0 end),
丁本年=sum(case when 供应商='丁' then 数量 else 0 end)
from @t
group by 品种分类,品种名称 with rollup
order by case when isnull(品种名称,品种分类) is null then 'zzzz'
else 品种分类+ltrim(len(isnull(品种名称,品种分类))) end/*
品种分类 本月合计 本年合计 甲本月 甲本年 乙本月 乙本年 丙本月 丙本年 丁本月 丁本年
---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
a类 200 400 200 400 0 0 0 0 0 0
A产品 100 300 100 300 0 0 0 0 0 0
A1产品 100 100 100 100 0 0 0 0 0 0
b类 200 600 0 0 200 600 0 0 0 0
B产品 200 600 0 0 200 600 0 0 0 0
C类 300 900 0 0 0 0 300 900 0 0
C产品 300 900 0 0 0 0 300 900 0 0
d类 400 1200 0 0 0 0 0 0 400 1200
d产品 400 1200 0 0 0 0 0 0 400 1200
合计 1100 3100 200 400 200 600 300 900 400 1200
*/
A1产品
A产品
B产品
C产品
C类
a类
b类
d产品
d类
create table tb(单据日期 datetime , 品种分类 varchar(10), 品种编号 varchar(10), 品种名称 varchar(10), 供应商 varchar(10), 数量 int)
insert into tb values('2008-10-1', 'a类' , '1001' , 'A产品' , '甲' , 100 )
insert into tb values('2008-10-1', 'b类' , '1002' , 'B产品' , '乙' , 200 )
insert into tb values('2008-10-1', 'C类' , '1003' , 'C产品' , '丙' , 300 )
insert into tb values('2008-10-1', 'd类' , '1004' , 'd产品' , '丁' , 400 )
insert into tb values('2008-11-1', 'a类' , '1001' , 'A产品' , '甲' , 100 )
insert into tb values('2008-11-1', 'a类' , '1001' , 'A1产品', '甲' , 100 )
insert into tb values('2008-11-1', 'b类' , '1002' , 'B产品' , '乙' , 200 )
insert into tb values('2008-11-1', 'C类' , '1003' , 'C产品' , '丙' , 300 )
insert into tb values('2008-11-1', 'd类' , '1004' , 'd产品' , '丁' , 400 )
insert into tb values('2008-12-1', 'a类' , '1001' , 'A产品' , '甲' , 100 )
insert into tb values('2008-12-1', 'b类' , '1002' , 'B产品' , '乙' , 200 )
insert into tb values('2008-12-1', 'C类' , '1003' , 'C产品' , '丙' , 300 )
insert into tb values('2008-12-1', 'd类' , '1004' , 'd产品' , '丁' , 400 )
go--SQL2000静态
select * from
(
select 品种分类,
sum(case when convert(varchar(7) , 单据日期,120)=convert(varchar(7),getdate(),120) then 数量 else 0 end) 合计_本月,
sum(case when year(单据日期)=year(getdate()) then 数量 else 0 end) 合计_本年,
sum(case when 供应商 = '甲' and convert(varchar(7) , 单据日期,120)=convert(varchar(7),getdate(),120) then 数量 else 0 end) 甲_本月,
sum(case when 供应商 = '甲' and year(单据日期)=year(getdate()) then 数量 else 0 end) 甲_本年,
sum(case when 供应商 = '乙' and convert(varchar(7) , 单据日期,120)=convert(varchar(7),getdate(),120) then 数量 else 0 end) 乙_本月,
sum(case when 供应商 = '乙' and year(单据日期)=year(getdate()) then 数量 else 0 end) 乙_本年,
sum(case when 供应商 = '丙' and convert(varchar(7) , 单据日期,120)=convert(varchar(7),getdate(),120) then 数量 else 0 end) 丙_本月,
sum(case when 供应商 = '丙' and year(单据日期)=year(getdate()) then 数量 else 0 end) 丙_本年,
sum(case when 供应商 = '丁' and convert(varchar(7) , 单据日期,120)=convert(varchar(7),getdate(),120) then 数量 else 0 end) 丁_本月,
sum(case when 供应商 = '丁' and year(单据日期)=year(getdate()) then 数量 else 0 end) 丁_本年
from tb
group by 品种分类
union all
select 品种名称,
sum(case when convert(varchar(7) , 单据日期,120)=convert(varchar(7),getdate(),120) then 数量 else 0 end) 合计_本月,
sum(case when year(单据日期)=year(getdate()) then 数量 else 0 end) 合计_本年,
sum(case when 供应商 = '甲' and convert(varchar(7) , 单据日期,120)=convert(varchar(7),getdate(),120) then 数量 else 0 end) 甲_本月,
sum(case when 供应商 = '甲' and year(单据日期)=year(getdate()) then 数量 else 0 end) 甲_本年,
sum(case when 供应商 = '乙' and convert(varchar(7) , 单据日期,120)=convert(varchar(7),getdate(),120) then 数量 else 0 end) 乙_本月,
sum(case when 供应商 = '乙' and year(单据日期)=year(getdate()) then 数量 else 0 end) 乙_本年,
sum(case when 供应商 = '丙' and convert(varchar(7) , 单据日期,120)=convert(varchar(7),getdate(),120) then 数量 else 0 end) 丙_本月,
sum(case when 供应商 = '丙' and year(单据日期)=year(getdate()) then 数量 else 0 end) 丙_本年,
sum(case when 供应商 = '丁' and convert(varchar(7) , 单据日期,120)=convert(varchar(7),getdate(),120) then 数量 else 0 end) 丁_本月,
sum(case when 供应商 = '丁' and year(单据日期)=year(getdate()) then 数量 else 0 end) 丁_本年
from tb
group by 品种名称
) t
order by left(品种分类,1) , case when charindex('类',品种分类) > 0 then 1 else 2 end
/*
品种分类 合计_本月 合计_本年 甲_本月 甲_本年 乙_本月 乙_本年 丙_本月 丙_本年 丁_本月 丁_本年
---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
a类 100 400 100 400 0 0 0 0 0 0
A1产品 0 100 0 100 0 0 0 0 0 0
A产品 100 300 100 300 0 0 0 0 0 0
b类 200 600 0 0 200 600 0 0 0 0
B产品 200 600 0 0 200 600 0 0 0 0
C类 300 900 0 0 0 0 300 900 0 0
C产品 300 900 0 0 0 0 300 900 0 0
d类 400 1200 0 0 0 0 0 0 400 1200
d产品 400 1200 0 0 0 0 0 0 400 1200(所影响的行数为 9 行)
*/--SQL2000动态
declare @sql1 varchar(8000)
set @sql1 = 'select 品种分类,sum(case when convert(varchar(7) , 单据日期,120)=convert(varchar(7),getdate(),120) then 数量 else 0 end) 合计_本月,
sum(case when year(单据日期)=year(getdate()) then 数量 else 0 end) 合计_本年 '
select @sql1 = @sql1 + ' , sum(case when 供应商 = ''' + 供应商 + ''' and convert(varchar(7) , 单据日期,120)=convert(varchar(7),getdate(),120) then 数量 else 0 end) [' + 供应商 + '_本月]'
+ ' , sum(case when 供应商 = ''' + 供应商 + ''' and year(单据日期)=year(getdate()) then 数量 else 0 end) [' + 供应商 + '_本年]'
from (select distinct 供应商 from tb) as a
set @sql1 = @sql1 + ' from tb group by 品种分类 '
declare @sql2 varchar(8000)
set @sql2 = 'select 品种名称,sum(case when convert(varchar(7) , 单据日期,120)=convert(varchar(7),getdate(),120) then 数量 else 0 end) 合计_本月,
sum(case when year(单据日期)=year(getdate()) then 数量 else 0 end) 合计_本年 '
select @sql2 = @sql2 + ' , sum(case when 供应商 = ''' + 供应商 + ''' and convert(varchar(7) , 单据日期,120)=convert(varchar(7),getdate(),120) then 数量 else 0 end) [' + 供应商 + '_本月]'
+ ' , sum(case when 供应商 = ''' + 供应商 + ''' and year(单据日期)=year(getdate()) then 数量 else 0 end) [' + 供应商 + '_本年]'
from (select distinct 供应商 from tb) as a
set @sql2 = @sql2 + ' from tb group by 品种名称 'exec('select * from (' + @sql1 + ' union all ' + @sql2 + ' ) t order by left(品种分类,1) , case when charindex(''类'',品种分类) > 0 then 1 else 2 end')
/*
品种分类 合计_本月 合计_本年 丙_本月 丙_本年 丁_本月 丁_本年 甲_本月 甲_本年 乙_本月 乙_本年
---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
a类 100 400 0 0 0 0 100 400 0 0
A1产品 0 100 0 0 0 0 0 100 0 0
A产品 100 300 0 0 0 0 100 300 0 0
b类 200 600 0 0 0 0 0 0 200 600
B产品 200 600 0 0 0 0 0 0 200 600
C类 300 900 300 900 0 0 0 0 0 0
C产品 300 900 300 900 0 0 0 0 0 0
d类 400 1200 0 0 400 1200 0 0 0 0
d产品 400 1200 0 0 400 1200 0 0 0 0*/
drop table tb
您好,lonlyhawk ,十分感谢您,您的方法结果和排序没有问题,但是我想问一下,
insert @t
select '2008-10-1','a类','1001','A产品','甲',100 union
select '2008-10-1','b类','1002','B产品','乙',200 union
select '2008-10-1','C类','1003','C产品','丙',300 union
select '2008-10-1','d类','1004','d产品','丁',400 union
select '2008-11-1','a类','1001','A产品','甲',100 union
select '2008-11-1','a类','1001-1','A1产品','甲',100 union
select '2008-11-1','b类','1002','B产品','乙',200 union
select '2008-11-1','C类','1003','C产品','丙',300 union
select '2008-11-1','d类','1004','d产品','丁',400 union
select '2008-12-1','a类','1001','A产品','甲',100 union
select '2008-12-1','b类','1002','B产品','乙',200 union
select '2008-12-1','C类','1003','C产品','丙',300 union
select '2008-12-1','d类','1004','d产品','丁',400
实际的数据量是很大的,几千条数据我肯定没办法这么insert,有没有别办法能解决。
--不固定列,用动态SQL
/*SELECT DISTINCT 供应商 COLLATE Chinese_PRC_90_BIN AS 供应商 FROM TEMP
还有ORDER BY CASE WHEN 品种名称 IS NULL AND 品种分类 IS NULL THEN 1 ELSE 0 END,品种分类,CASE WHEN 品种名称 IS NOT NULL THEN 1 ELSE 0 END 是为了解决排序问题
*/
DECLARE @sql NVARCHAR(4000)
SET @sql = '
SELECT CASE WHEN 品种名称 IS NULL AND 品种分类 IS NOT NULL THEN 品种分类
WHEN 品种名称 IS NULL AND 品种分类 IS NULL THEN ''总计''
ELSE 品种名称 END 品种
,SUM(本月数量) AS 本月合计
,SUM(本年数量) AS 本年合计 '
SELECT @sql = @sql + ',SUM(CASE WHEN 供应商 = ''' + 供应商 + ''' THEN 本月数量 ELSE 0 END) AS [本月' + 供应商 + ']
,SUM(CASE WHEN 供应商 = ''' + 供应商 + ''' THEN 本年数量 ELSE 0 END) AS [本年' + 供应商 + ']'
FROM (SELECT DISTINCT 供应商 COLLATE Chinese_PRC_90_BIN AS 供应商 FROM TEMP)AS A
ORDER BY 供应商 DESCSET @sql = @sql + ' FROM (SELECT 品种分类
,品种名称
,供应商
,SUM(CASE WHEN CONVERT(VARCHAR(4),单据日期,112)=CONVERT(VARCHAR(4),GETDATE(),112) THEN 数量 ELSE 0 END) AS 本年数量
,SUM(CASE WHEN CONVERT(VARCHAR(6),单据日期,112)=CONVERT(VARCHAR(6),GETDATE(),112) THEN 数量 ELSE 0 END) AS 本月数量
FROM TEMP
GROUP BY 品种分类,品种名称,供应商
) xx GROUP BY 品种分类,品种名称 WITH ROLLUP
ORDER BY CASE WHEN 品种名称 IS NULL AND 品种分类 IS NULL THEN 1 ELSE 0 END,品种分类,CASE WHEN 品种名称 IS NOT NULL THEN 1 ELSE 0 END
'EXEC(@sql)