SQLServer2005 Pivot 转置使用动态列
|
转载自：http://www.cbf107.com/CBF107Item.aspx?ID=79c98c3e-aea2-46f9-baa7-1ccd067e2a81 最近项目中用到 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 (), 300, ' 医疗保险 ' ) insert into Insurances values ( newid (), 2, ' 李四 ' , 250, 350, 3, ' 王二 ' , 150, ' 养老保险 ' ) insert into Insurances values ( newid (), ' 医疗保险 ' ) ? 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, 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,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 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
