一个数据库的列变行问题
从表1得到下面的结果
表1
年份 月份 销售量 天数
2001 5 400 4
2001 6 400 8
2001 7 400 5
2002 5 400 10
2003 2 400 12
得到 <font color=#ff0033> 每年每个月每天销售数量(表1销售量/天数) </font>
如2001 年
年份 1月(每天销售量) 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月
2001 100 50 80
[解决办法]
create table T1(年份 int, 月份 int, 销售量 int, 天数 int)
insert into T1
select 2001, 5, 400, 4 union all
select 2001, 6, 400, 8 union all
select 2001, 7, 400, 5 union all
select 2002, 5, 400, 10 union all
select 2003, 2, 400, 12
select
年份,
cast(sum(case when 月份=1 then 销售量 else null end)*1.0/max(case when 月份=1 then 天数 else 0 end) as decimal(10,2)) as [1月],
cast(sum(case when 月份=2 then 销售量 else null end)*1.0/max(case when 月份=2 then 天数 else 0 end) as decimal(10,2)) as [2月],
cast(sum(case when 月份=3 then 销售量 else null end)*1.0/max(case when 月份=3 then 天数 else 0 end) as decimal(10,2)) as [3月],
cast(sum(case when 月份=4 then 销售量 else null end)*1.0/max(case when 月份=4 then 天数 else 0 end) as decimal(10,2)) as [4月],
cast(sum(case when 月份=5 then 销售量 else null end)*1.0/max(case when 月份=5 then 天数 else 0 end) as decimal(10,2)) as [5月],
cast(sum(case when 月份=6 then 销售量 else null end)*1.0/max(case when 月份=6 then 天数 else 0 end) as decimal(10,2)) as [6月],
cast(sum(case when 月份=7 then 销售量 else null end)*1.0/max(case when 月份=7 then 天数 else 0 end) as decimal(10,2)) as [7月],
cast(sum(case when 月份=8 then 销售量 else null end)*1.0/max(case when 月份=8 then 天数 else 0 end) as decimal(10,2)) as [8月],
cast(sum(case when 月份=9 then 销售量 else null end)*1.0/max(case when 月份=9 then 天数 else 0 end) as decimal(10,2)) as [9月],
cast(sum(case when 月份=10 then 销售量 else null end)*1.0/max(case when 月份=10 then 天数 else 0 end) as decimal(10,2)) as [10月],
cast(sum(case when 月份=11 then 销售量 else null end)*1.0/max(case when 月份=11 then 天数 else 0 end) as decimal(10,2)) as [11月],
cast(sum(case when 月份=12 then 销售量 else null end)*1.0/max(case when 月份=12 then 天数 else 0 end) as decimal(10,2)) as [12月]
from T1
group by 年份
drop table T1
[解决办法]
--创建测试环境
create table T1(年份 int, 月份 int, 销售量 int, 天数 int)
--追加测试数据
insert into T1
select 2001, 5, 400, 4 union all
select 2001, 6, 400, 8 union all
select 2001, 7, 400, 5 union all
select 2002, 5, 400, 10 union all
select 2003, 2, 400, 12
--你要求的SQL语句
select
年份,
cast(sum(case when 月份=1 then 销售量 else null end)*1.0/max(case when 月份=1 then 天数 else 0 end) as decimal(10,2)) as [1月],
cast(sum(case when 月份=2 then 销售量 else null end)*1.0/max(case when 月份=2 then 天数 else 0 end) as decimal(10,2)) as [2月],
cast(sum(case when 月份=3 then 销售量 else null end)*1.0/max(case when 月份=3 then 天数 else 0 end) as decimal(10,2)) as [3月],
cast(sum(case when 月份=4 then 销售量 else null end)*1.0/max(case when 月份=4 then 天数 else 0 end) as decimal(10,2)) as [4月],
cast(sum(case when 月份=5 then 销售量 else null end)*1.0/max(case when 月份=5 then 天数 else 0 end) as decimal(10,2)) as [5月],
cast(sum(case when 月份=6 then 销售量 else null end)*1.0/max(case when 月份=6 then 天数 else 0 end) as decimal(10,2)) as [6月],
cast(sum(case when 月份=7 then 销售量 else null end)*1.0/max(case when 月份=7 then 天数 else 0 end) as decimal(10,2)) as [7月],
cast(sum(case when 月份=8 then 销售量 else null end)*1.0/max(case when 月份=8 then 天数 else 0 end) as decimal(10,2)) as [8月],
cast(sum(case when 月份=9 then 销售量 else null end)*1.0/max(case when 月份=9 then 天数 else 0 end) as decimal(10,2)) as [9月],
cast(sum(case when 月份=10 then 销售量 else null end)*1.0/max(case when 月份=10 then 天数 else 0 end) as decimal(10,2)) as [10月],
cast(sum(case when 月份=11 then 销售量 else null end)*1.0/max(case when 月份=11 then 天数 else 0 end) as decimal(10,2)) as [11月],
cast(sum(case when 月份=12 then 销售量 else null end)*1.0/max(case when 月份=12 then 天数 else 0 end) as decimal(10,2)) as [12月]
from T1
group by 年份
--删掉测试表
drop table T1
[解决办法]
CREATE TABLE tb
(
年份 INT,
月份 INT,
销售量 INT,
天数 INT
)
INSERT INTO tb
SELECT 2001,5,400,4 UNION ALL
SELECT 2001,6,400,8 UNION ALL
SELECT 2001,7,400,5 UNION ALL
SELECT 2002,5,400,10 UNION ALL
SELECT 2003,2,400,12
GO
--建立辅助月份表
CREATE TABLE MON
(
MON INT
)
INSERT INTO MON
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9 UNION ALL
SELECT 10 UNION ALL
SELECT 11 UNION ALL
SELECT 12
go
--行转列
declare @s varchar(8000)
set @s= 'SELECT 年份 '
select @s=@s+ ',isnull(MAX(case 月份 when '+CAST(MON AS VARCHAR)+ ' then 销售量/天数 end),0) as M '+CAST(MON AS VARCHAR)+ '月 ' from mon
SET @s=@s+ ' FROM tb GROUP BY 年份 '
exec(@s)
--结果
年份 M1月 M2月 M3月 M4月 M5月 M6月 M7月 M8月 M9月 M10月 M11月 M12月
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2001 0 0 0 0 100 50 80 0 0 0 0 0
2002 0 0 0 0 40 0 0 0 0 0 0 0
2003 0 33 0 0 0 0 0 0 0 0 0 0
警告: 聚合或其他 SET 操作消除了空值。
(3 行受影响)
[解决办法]
if object_id( 'pubs..tb ') is not null
drop table tb
go
create table tb(年份 int,月份 int,销售量 int,天数 int)
insert into tb(年份,月份,销售量,天数) values(2001,5,400,4)
insert into tb(年份,月份,销售量,天数) values(2001,6,400,8)
insert into tb(年份,月份,销售量,天数) values(2001,7,400,5)
insert into tb(年份,月份,销售量,天数) values(2002,5,400,10)
insert into tb(年份,月份,销售量,天数) values(2003,2,400,12)
go
select 年份,
max(case 月份 when 1 then 销售量/天数 else 0 end) '1月 ' ,
max(case 月份 when 2 then 销售量/天数 else 0 end) '2月 ' ,
max(case 月份 when 3 then 销售量/天数 else 0 end) '3月 ' ,
max(case 月份 when 4 then 销售量/天数 else 0 end) '4月 ' ,
max(case 月份 when 5 then 销售量/天数 else 0 end) '5月 ' ,
max(case 月份 when 6 then 销售量/天数 else 0 end) '6月 ' ,
max(case 月份 when 7 then 销售量/天数 else 0 end) '7月 ' ,
max(case 月份 when 8 then 销售量/天数 else 0 end) '8月 ' ,
max(case 月份 when 9 then 销售量/天数 else 0 end) '9月 ' ,
max(case 月份 when 10 then 销售量/天数 else 0 end) '10月 ' ,
max(case 月份 when 11 then 销售量/天数 else 0 end) '11月 ' ,
max(case 月份 when 12 then 销售量/天数 else 0 end) '12月 '
from tb
group by 年份
drop table tb
/*
年份 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月
---- --- --- --- --- --- --- --- --- --- ---- ---- -----------
2001 0 0 0 0 100 50 80 0 0 0 0 0
2002 0 0 0 0 40 0 0 0 0 0 0 0
2003 0 33 0 0 0 0 0 0 0 0 0 0
（所影响的行数为 3 行）
*/
[解决办法]
--上面为静态SQL,下面为动态SQL.
if object_id( 'pubs..tb ') is not null
drop table tb
go
create table tb(年份 int,月份 int,销售量 int,天数 int)
insert into tb(年份,月份,销售量,天数) values(2001,5,400,4)
insert into tb(年份,月份,销售量,天数) values(2001,6,400,8)
insert into tb(年份,月份,销售量,天数) values(2001,7,400,5)
insert into tb(年份,月份,销售量,天数) values(2002,5,400,10)
insert into tb(年份,月份,销售量,天数) values(2003,2,400,12)
go
declare @sql varchar(8000)
set @sql = 'select 年份 '
select @sql = @sql + ' , max(case 月份 when ' ' ' + cast(月份 as varchar) + ' ' ' then 销售量/天数 else 0 end) [ ' + cast(月份 as varchar) + '月] '
from (select distinct 月份 from tb) as a
set @sql = @sql + ' from tb group by 年份 '
exec(@sql)
drop table tb
/*
年份 2月 5月 6月 7月
----------- ----------- ----------- ----------- -----------
2001 0 100 50 80
2002 0 40 0 0
2003 33 0 0 0
*/
