SQLServer2005 Pivot 转置使用动态列
来源:互联网 发布:格美直饮水机编程视频 编辑:程序博客网 时间:2024/05/21 21:49
转载自:http://www.cbf107.com/CBF107Item.aspx?ID=79c98c3e-aea2-46f9-baa7-1ccd067e2a81
SQLServer2005 Pivot 转置使用动态列(应用到视图)
最近项目中用到Pivot 对表进行转置,遇到一些问题,主要是Pivot 转置的时候没有办法动态产生转置列名,而作视图的时候又很需要动态的产生这些列,百度上似乎也没有找的很满意的答案,在google上搜到一老外的解决方案,现在自己总结了一下,希望给用的上的朋友一些帮助。
1.创建表脚本
if exists (select 1
from sysobjects
where id = object_id('Insurances')
and type = 'U')
drop table Insurances
go
/*==============================================================*/
/* Table: Insurances */
/*==============================================================*/
create table Insurances (
RefID uniqueidentifier not null,
HRMS nvarchar(20) null,
Name nvarchar(20) null,
InsuranceMoney money null,
InsuranceName nvarchar(100) not null,
constraint PK_INSURANCES primary key (RefID)
)
go
2.测试数据脚本
insert into Insurances values (newid(),1,'张三',200,'养老保险')
insert into Insurances values (newid(),1,'张三',300,'医疗保险')
insert into Insurances values (newid(),2,'李四',250,'养老保险')
insert into Insurances values (newid(),2,'李四',350,'医疗保险')
insert into Insurances values (newid(),3,'王二',150,'养老保险')
insert into Insurances values (newid(),3,'王二',300,'医疗保险')
3.查询表数据
select HRMS,Name,InsuranceMoney,InsuranceName From Insurances
HRMS Name InsuranceMoney InsuranceName
-------------------- -------------------- --------------------- ----------
1 张三 200.00 养老保险
2 李四 350.00 医疗保险
2 李四 250.00 养老保险
1 张三 300.00 医疗保险
3 王二 300.00 医疗保险
3 王二 150.00 养老保险
4.转置表数据
select * from
(
select HRMS,Name,InsuranceMoney,InsuranceName from Insurances
) p
Pivot (
sum(InsuranceMoney)
FOR InsuranceName IN
( [医疗保险], [养老保险]))
as pvt
HRMS Name 医疗保险 养老保险
-------------------- -------------------- --------------------- ---------------------
2 李四 350.00 250.00
3 王二 300.00 150.00
1 张三 300.00 200.00
5.偶的问题
这个语句中 医疗保险、养老保险 是SQL语句中写死的,而且Sql2005中这个代码没有办法使用动态的查询结果集
5.存储过程解决问题
所以如果要动态的完成个脚本,可以先拼出SQL 然后通过exec sp_executesql 执行
实现存储过程
create procedure InsurancePivot
as
Begin
DECLARE @ColumnNames VARCHAR(3000)
SET @ColumnNames=''
SELECT
@ColumnNames = @ColumnNames + '[' + InsuranceName + '],'
FROM
(
SELECT DISTINCT InsuranceName FROM Insurances
) t
SET @ColumnNames= LEFT(@ColumnNames, LEN(@ColumnNames)-1)
DECLARE @selectSQL NVARCHAR(3000)
SET @selectSQL=
'SELECT HRMS,Name,{0} FROM
(
SELECT HRMS,Name,InsuranceMoney,InsuranceName FROM Insurances
) p
Pivot( Max(InsuranceMoney) For InsuranceName in ({0})) AS pvt
ORDER BY HRMS'
SET @selectSQL= REPLACE(@selectSQL,'{0}',@ColumnNames)
exec sp_executesql @selectSQL
end
测试存储过程:
exec InsurancePivot
HRMS Name 养老保险 医疗保险
-------------------- -------------------- --------------------- ---------------------
1 张三 200.00 300.00
2 李四 250.00 350.00
3 王二 150.00 300.00
6.关于视图的新问题和解决方案
在视图中没有办法直接调用这个存储过程,但是我们在做程序、做报表的时候又非常需要
其实可以通过OPENQUERY来实现(这是一个非正规的解决方式,但目前可以实现)
(另外可以使用OPENROWSET,但是参数太多偶放弃了)
使用OPENQUERY 的格式是:OPENQUERY([链接服务器],’sql语句’)
因为是当前数据的视图, 链接服务器可以通过属性查看,MSCBF107 是我测试的链接服务器
也可以通过sp_helpserver 查看
下面这句话也非常重要,使用的朋友替换[MSCBF107]就ok了,否则使用OPENQUERY会出现未将服务器'MSCBF107' 配置为用于DATA ACCESS
sp_serveroption [MSCBF107], 'Data Access', 'True'
创建视图如下:
create view InsurancePivotView
as
select *From OPENQUERY ([MSCBF107],N'SET FMTONLY OFF;exec test.dbo.InsurancePivot')
测试视图就可以得到想要的结果了
select *from InsurancePivotView
- SQLServer2005 Pivot 转置使用动态列
- 使用pivot将行转成列
- Sql Server 列转行 Pivot使用
- 动态SQL解决PIVOT透视多列的问题
- SQL 2008行列转换的pivot--产生动态列
- 动态数据透视表pivot table(行转换列)
- SQL中行列转换 Pivot UnPivot 行列转化与PIVOT、UNPIVOT运算符使用
- 使用 PIVOT 和 UNPIVOT(SQL2005的行转列、列转行)
- 使用pivot和unpivot语句实现行转列,列转行
- 【转】SQL Server 动态行转列(PIVOT)
- Oracle SQL函数pivot、unpivot转置函数实现行转列、列转行
- sqlserver2008 PIVOT列转行
- Pivot 多列转行
- pivot实现列转行
- PIVOT使用
- pivot使用
- 【转】SQL Server中行列转换 Pivot UnPivot
- sqlserver2005 中行列转换
- 我在入侵过程中注意到的细节
- You are attempting to build on a 32-bit system.Only 64-bit build environments are supported beyond froyo/2.2.
- IOC/DI与AOP概念的理解
- 通往私有云的荆棘路连载 – 第三部分: 准备好了么?
- Java容器类List、ArrayList、Vector及map、HashTable、HashMap分别的区别
- SQLServer2005 Pivot 转置使用动态列
- 转载:android adb 总结
- Froyo 自动编译方法
- IceBox实践
- 将minicom移植到mini2440(tq2440)
- Android中Log机制详解
- 对工作的态度
- 从火车站到中国馆
- 安装iphone软件