1.使用pivot进行报表的行转列 收藏
行转列在报表中是用的很多的,以前在sql中使用横转列的时候总是使用selet case来处理,即便sql2005现在都要被sql2008代替了,偶还一次没有用过他提供的新东东pivot。一时兴起,还是学了学。
有两个简化的表(供应商表和供货记录表)
create table supplier (sid int,suppliershortname varchar(20))--记录供应商的简称和代码
create table warehouserecord (sid int,materialid int,qty numeric(18,2),price numeric(18,4),billdate smalldatetime)--记录每天供应商的送货记录
管理需求上下面这个是经常遇到的,需要知道最近一个月或一周内每个供应商的供货情况,可能需要得到的表样式是下面的
日期 供应商A供货金额 供应商B供货金额 供应商C供货金额 .....
2008-11-25 300.2 250.0 2562.65
我们创建好表后填入记录,开始下面的工作
--写入供应商记录
insert into supplier (sid,suppliershortname) select 1,'供应商A'
insert into supplier (sid,suppliershortname) select 2,'供应商B'
insert into supplier (sid,suppliershortname) select 3,'供应商C'
--送货记录
insert into warehouserecord (sid,materialid,qty,price,billdate) select 1,10000,200,5.4,'2008-11-25'
insert into warehouserecord (sid,materialid,qty,price,billdate) select 2,11000,30,95.4,'2008-11-25'
insert into warehouserecord (sid,materialid,qty,price,billdate) select 1,15000,50,6.32,'2008-11-25'
insert into warehouserecord (sid,materialid,qty,price,billdate) select 3,11000,220,9.2,'2008-11-25'
要达到我们需要的效果,如果还是使用sql 2000中的方式我们也很容易
select c.billdate,
sum(case when c.suppliershortname='供应商A' then c.qty*c.price else 0 end) as 供应商A,
sum(case when c.suppliershortname='供应商b' then c.qty*c.price else 0 end) as 供应商B,
sum(case when c.suppliershortname='供应商C' then c.qty*c.price else 0 end) as 供应商c
from (select a.suppliershortname,b.* from supplier a,warehouserecord b where a.sid=b.sid) c group by c.billdate
这样就可以得到我们的结果了,如果供应商比较多,是不是有点长了。
写完了sql2000的,现在如果换成了sql2005的pivot,则是如何写的呢?
select billdate,[供应商A],[供应商B],[供应商C] from
(select a.suppliershortname,sum(b.qty*b.price) as daymoney,b.billdate from supplier a,warehouserecord b where a.sid=b.sid group by b.billdate,a.suppliershortname) c
pivot (sum(daymoney) for suppliershortname in ([供应商A],[供应商B],[供应商C])) AS unpvt
看起来好像只是语句短了点,其他的差别不大吧。
上面两种方法写的都是固定字段的,如果供应商很多,或者要监控的供应商是一直变化的,按上面那样方法写还不死人啊。
对于不固定列的,sql2000是如何写的呢?
declare @sql varchar(8000)
set @sql = 'select billdate '
select @sql = @sql + ' , sum(case c.suppliershortname when ''' + suppliershortname + ''' then c.price*c.qty else 0 end) [' + suppliershortname + ']'
from (select distinct suppliershortname from supplier) as a
set @sql = @sql + ' from (select a.suppliershortname,b.* from supplier a,warehouserecord b where a.sid=b.sid) c group by c.billdate'
exec(@sql)
执行的结果就和上面的一样了。
那么使用sql2005提供的新方法pivot又是如何写的呢?
DECLARE @selstr varchar(1000)
declare @sql varchar(8000)
SELECT @selstr = isnull(@selstr + ',','')+ '['+ltrim(suppliershortname)+']'
FROM (SELECT suppliershortname FROM supplier) d
set @sql='select billdate,'+@selstr+' from (select a.suppliershortname,b.billdate,sum(b.qty*b.price) as daymoney from supplier a,warehouserecord b where a.sid=b.sid group by b.billdate,a.suppliershortname) c '
set @sql=@sql+'pivot (sum(daymoney) for suppliershortname in ('+@selstr+')) AS unpvt'
print @sql
exec(@sql)
感觉出差别来了没有?
说真的,就自己的感觉,我还是觉得以前的方法比较好理解一点。也许人总是活着习惯中,当你习惯一种方法时,改变是真的有点困难,除非你努力将新方法成为了你新的习惯。
2.
.Pivot的用法体会:
语句范例:
select PN,[2006/5/30] as [20060530],[2006/6/2] as [20060602]
from consumptiondata a
Pivot (sum(a.M_qty) FOR a.M_date in ([2006/5/30],[2006/6/2])) as PVT
order by PN
Table结构 Consumptiondata (PN,M_Date,M_qty)
order by PN可要可不要,并不重要,只是排序的作用
关键的是红色部分,解析如下,select 大家都知道,PN是 ConsumptionData表中的一个Column,
[2006/5/30]也是一个Column,他需要显示成[20060530],注意[2006/5/30]不是一个Value,而是一个Column.[2006/6/2]与[2006/5/30]一样.
Pivot ( ........... ) as PVT这个结构是固定格式,没有什么需要特殊说明的,当然PVT随便你给他一个 NICKNAME ,it doesn't make any differences.
sum(a.M_qty) 是我们希望显示出来的值,注意这个地方必须用汇总函数,否则语法不会过.
FOR a.M_date in ([2006/5/30],[2006/6/2])for 表示汇总的值要显示在哪一个Column下面
如果我们想让Sum(M_qty)显示在PN转换的Column下面,则可写为For PN, in 的清单表示我们关注哪些要查看的Column,注意再次强调是Column,不是Value. in的清单是Column清单,不是Value清单,是M_date的Value转换成的Column清单.
2.UnPivot
--此段可以直接在Sql 2005中执行
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int)
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4)
INSERT INTO pvt VALUES (2,4,1,5,5,5)
INSERT INTO pvt VALUES (3,4,3,5,4,4)
INSERT INTO pvt VALUES (4,4,2,5,5,4)
INSERT INTO pvt VALUES (5,5,1,5,5,5)
GO
--select * from PVT
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM PVT
UNPIVOT (
Orders FOR Employee IN ([Emp1], [Emp2], [Emp3], [Emp4], [Emp5])
)AS unpvt
GO
分享到:
相关推荐
使用原有的行转列方法一个一个数据处理比较繁琐 ,使用Piovt方法进行数据行转列处理,就很方便快捷的实现预期的结果。 使用 PIVOT 和 UNPIVOT 关系运算符将表值表达式更改为另一个表。PIVOT 通过将表达式某一列中的...
oracle数据库最详细的行转列资料,是官方文档的详细介绍版,中文的
长数据宽数据转换,pandas 一列拆分多列,pandas pivot——table使用,pandas.melt 使用
pivot 与 unpivot 函数是SQL05新提供的2个函数 灰常灰常的实用
主流数据(ORACLE,DB2)行转列实例,包括2种实现方式,通过case..when和left join..on;大牛补充其他的实现方式。
SQL行列转换 Pivot UnPivot
有case when方式和2005之后的内置pivot和unpivot方法来实现,行列互转,可以分为静态互转,动态互转。
NULL 博文链接:https://vernonchen163.iteye.com/blog/1902976
枢纽表达式PIVOT和UNPIVOT的简单用法 sql 列转成行 sql server 2005
SQL之行转列 SQL之行转列 SQL之行转列
SQL行转列学习谢谢!
Oracle 11g中的pivot和unpivot转换操作
带条件查询的参数化动态PIVOT行转列,方便快速完成行转列数据处理。
利用SQL语句将表行转列显示,应用的是PIVOT ,很方便
在数据库操作中,有些时候我们遇到需要实现“行转列”的需求,例如一下的表为某店铺的一周收入情况表: WEEK_INCOME(WEEK VARCHAR(10),INCOME DECIMAL) 我们先插入一些模拟数据: INSERT INTO WEEK_INCOME SELECT ...
实现的方法,有case when方式和2005之后的内置pivot和unpivot方法来实现。 在读了技术内幕那一节后,虽说这些解决方案早就用过了,却没有系统性的认识和总结过。为了加深认识,再总结一次。 行列互转,可以分为静态...
SQL中,在SQL2005以上有更方便的方式,pivot新式行转列使用方法
主要给大家介绍了关于SQL基础教程之行转列Pivot函数的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者使用SQL具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧
通过datastage实现输入数据如下: ...REPID last_name Q1sales Pivot_index 100 Smith 1234.08 0 100 Smith 1456.80 1 100 Smith 1578.00 2 101 Yamada 1245.20 0 101 Yamada 1765.00 1 101 Yamada 1934.22 2
PIVOT用于将列值旋转为列名 C# PIVOT用于将列值旋转为列名 C#