SQLServer2005 Pivot 转置使用动态列

概述转载自：http://www.cbf107.com/CBF107Item.aspx?ID=79c98c3e-aea2-46f9-baa7-1ccd067e2a81 SQLServer2005 Pivot 转置使用动态列（应用到视图） 最近项目中用到 Pivot 对表进行转置，遇到一些问题，主要是 Pivot 转置的时候没有办法动态产生转置列名 , 而作视图的时候又很需要动态的产生这些列，百度上似乎

转载自：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 (), 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

总结

以上是内存溢出为你收集整理的SQLServer2005 Pivot 转置使用动态列全部内容，希望文章能够帮你解决SQLServer2005 Pivot 转置使用动态列所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。