表A中的数据是产品名,存库量,库存年份,类名,库存月份ProductName ,StockNum ,StockYear,CategoryName ,StockDate
想要SELECT 出来类名,产品名,库存年份,1月库存量,2月库存量……12月库存量,总计CategoryName ,ProductName ,StockYear,m1……m12,totalStockDate ='2008-1-1'StockYear='2008'StockNum =13有点类似行列转换。把该产品的数量横向显示
想要SELECT 出来类名,产品名,库存年份,1月库存量,2月库存量……12月库存量,总计CategoryName ,ProductName ,StockYear,m1……m12,totalStockDate ='2008-1-1'StockYear='2008'StockNum =13有点类似行列转换。把该产品的数量横向显示
解决方案 »
- 求SQL语句:存在就跳过,不存在就插入记录,从字段ID=1开始,到字段ID=1某个值结束?
- 关于SQL Server 2000端口无法启动问题
- 请教,我现在的机器上已经装了SQL Express,想要保持现在的设定,升级到SQL2005,怎样做最简便?
- 插入100万条记录
- sqlserver nested triggers问题
- 关于在函数内不正确地使用了 'getdate'的问题
- 加工单价工序计算请教
- 我如果对数据库中的日期型字段这样操作convert(char(8),日期型字段,120)可不可以,效率如何?
- 求救exec 的用法,分不够还有!
- 如何快速查看一个数据库中的所有的触发器?
- 动态SQL语句求教,在线等。
- MSSQL的自动编号,如何指定特定的类型呀?
--try:
select CategoryName,ProductName,StockYear,
m1=sum(case when 1 then StockNum else 0 end),
m2=sum(case when 2 then StockNum else 0 end),
m3=sum(case when 3 then StockNum else 0 end),
m4=sum(case when 4 then StockNum else 0 end),
m5=sum(case when 5 then StockNum else 0 end),
m6=sum(case when 6 then StockNum else 0 end),
m7=sum(case when 7 then StockNum else 0 end),
m8=sum(case when 8 then StockNum else 0 end),
m9=sum(case when 9 then StockNum else 0 end),
m10=sum(case when 10 then StockNum else 0 end),
m11=sum(case when 11 then StockNum else 0 end),
m12=sum(case when 12 then StockNum else 0 end)
from 表名
group by CategoryName,ProductName,StockYear
--掉了字段:select CategoryName,ProductName,StockYear,
m1=sum(case StockDate when 1 then StockNum else 0 end),
m2=sum(case StockDate when 2 then StockNum else 0 end),
m3=sum(case StockDate when 3 then StockNum else 0 end),
m4=sum(case StockDate when 4 then StockNum else 0 end),
m5=sum(case StockDate when 5 then StockNum else 0 end),
m6=sum(case StockDate when 6 then StockNum else 0 end),
m7=sum(case StockDate when 7 then StockNum else 0 end),
m8=sum(case StockDate when 8 then StockNum else 0 end),
m9=sum(case StockDate when 9 then StockNum else 0 end),
m10=sum(case StockDate when 10 then StockNum else 0 end),
m11=sum(case StockDate when 11 then StockNum else 0 end),
m12=sum(case StockDate when 12 then StockNum else 0 end)
from 表名
group by CategoryName,ProductName,StockYear
select
CategoryName,ProductName,StockYear,
m1=sum(case StockDate when 1 then StockNum else 0 end),
m2=sum(case StockDate when 2 then StockNum else 0 end),
m3=sum(case StockDate when 3 then StockNum else 0 end),
m4=sum(case StockDate when 4 then StockNum else 0 end),
m5=sum(case StockDate when 5 then StockNum else 0 end),
m6=sum(case StockDate when 6 then StockNum else 0 end),
m7=sum(case StockDate when 7 then StockNum else 0 end),
m8=sum(case StockDate when 8 then StockNum else 0 end),
m9=sum(case StockDate when 9 then StockNum else 0 end),
m10=sum(case StockDate when 10 then StockNum else 0 end),
m11=sum(case StockDate when 11 then StockNum else 0 end),
m12=sum(case StockDate when 12 then StockNum else 0 end),
sum(StockNum) as total
from
表名
group by
CategoryName,ProductName,StockYear
select CategoryName,ProductName,StockYear,
m1=sum(case StockDate when 1 then StockNum else 0 end),
m2=sum(case StockDate when 2 then StockNum else 0 end),
m3=sum(case StockDate when 3 then StockNum else 0 end),
m4=sum(case StockDate when 4 then StockNum else 0 end),
m5=sum(case StockDate when 5 then StockNum else 0 end),
m6=sum(case StockDate when 6 then StockNum else 0 end),
m7=sum(case StockDate when 7 then StockNum else 0 end),
m8=sum(case StockDate when 8 then StockNum else 0 end),
m9=sum(case StockDate when 9 then StockNum else 0 end),
m10=sum(case StockDate when 10 then StockNum else 0 end),
m11=sum(case StockDate when 11 then StockNum else 0 end),
m12=sum(case StockDate when 12 then StockNum else 0 end),
sum(StockNum) as total
from A
group by
CategoryName,ProductName,StockYear
m1=sum(case StockDate when 1 then StockNum else 0 end),
m2=sum(case StockDate when 2 then StockNum else 0 end),
m3=sum(case StockDate when 3 then StockNum else 0 end),
m4=sum(case StockDate when 4 then StockNum else 0 end),
m5=sum(case StockDate when 5 then StockNum else 0 end),
m6=sum(case StockDate when 6 then StockNum else 0 end),
m7=sum(case StockDate when 7 then StockNum else 0 end),
m8=sum(case StockDate when 8 then StockNum else 0 end),
m9=sum(case StockDate when 9 then StockNum else 0 end),
m10=sum(case StockDate when 10 then StockNum else 0 end),
m11=sum(case StockDate when 11 then StockNum else 0 end),
m12=sum(case StockDate when 12 then StockNum else 0 end)
from 表名
group by CategoryName,ProductName,StockYear
---测试数据
CREATE TABLE T_Stock
(ProductName varchar(80),
StockNum decimal(8),
StockYear int,
CategoryName varchar(80),
StockDate varchar(80)
)
insert into T_Stock
select '香蕉',1600,2008,'水果','1月份'
union all
select '苹果',300,2008,'水果','1月份'
union all
select '哈密瓜',60,2008,'水果','2月份'
union all
select '香蕉',80,2008,'水果','9月份'
union all
select '香蕉',70,2009,'水果','3月份'
union all
select '香蕉',50,2008,'水果','2月份'
union all
select '香蕉',500,2008,'水果','1月份'
union all
select '香蕉',70,2008,'水果','4月份'
union all
select '香蕉',90,2009,'水果','1月份'
union all
select '香蕉',500,2008,'水果','6月份'
union all
select '香蕉',750,2007,'水果','1月份'
union all
select '香蕉',600,2008,'水果','9月份'
union all
select '香蕉',305,2008,'水果','1月份'
union all
select '香蕉',860,2008,'水果','10月份'
union all
select '香蕉',403,2008,'水果','1月份'
union all
select '香蕉',520,2008,'水果','11月份'
union all
select '香蕉',354,2008,'水果','12月份'
union all
select '苹果',386,2008,'水果','2月份'
---------------------------------------
---------------------------------------
Declare @Sql varchar(8000)
set @Sql='select ProductName as 产品名'+',StockYear as 库存年份' + ',sum(StockNum) as 总数量'
select @Sql=@Sql + ' , max(case StockDate when ''' + StockDate + ''' then StockNum else 0 end) as [' + StockDate + '数量' + ']'
from (select distinct StockDate from T_Stock ) as a
set @Sql = @Sql + 'from T_Stock group by ProductName,StockYear'
exec(@sql)
--结果
产品名 库存年份 总数量 10月份数量 11月份数量 12月份数量 1月份数量 2月份数量 3月份数量 4月份数量 6月份数量 9月份数量
香蕉 2007 750 0 0 0 750 0 0 0 0 0
哈密瓜 2008 60 0 0 0 0 60 0 0 0 0
苹果 2008 686 0 0 0 300 386 0 0 0 0
香蕉 2008 5842 860 520 354 1600 50 0 70 500 600
香蕉 2009 160 0 0 0 90 0 70 0 0 0