SQLServer动态行转列(参数化表名、分组列、行转列字段、字段值).pdf

《SQLServer动态行转列(参数化表名、分组列、行转列字段、字段值).pdf》由会员分享，可在线阅读，更多相关《SQLServer动态行转列(参数化表名、分组列、行转列字段、字段值).pdf（7页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、SQL Server 动态行转列（参数化表名、分组列、行转列字段、字段值）一.本文所涉及的内容（Contents）本文所涉及的内容（Contents）背景（Contexts）实现代码（SQL Codes）方法一：使用拼接 SQL，静态列字段；方法二：使用拼接 SQL，动态列字段；方法三：使用 PIVOT 关系运算符，静态列字段；方法四：使用 PIVOT 关系运算符，动态列字段；扩展阅读一：参数化表名、分组列、行转列字段、字段值；扩展阅读二：在前面的基础上加入条件过滤；二.背景（Contexts）其实行转列并不是一个什么新鲜的话题了，甚至已经被大家说到烂了，网上的很多例子多多少少都有些问题，所以

2、我希望能让大家快速的看到执行的效果，所以在动态列的基础上再把表、分组字段、行转列字段、值这四个行转列固定需要的值变成真正意义的参数化，大家只需要根据自己的环境，设置参数值，马上就能看到效果了。行转列的效果图如图 1 所示：（图 1：行转列效果图）(一)首先我们先创建一个测试表，往里面插入测试数据，返回表记录如图 2 所示：-创建测试表 IF EXISTS(SELECT *FROM sys.objects WHERE object_id=OBJECT_ID(Ndbo.TestRows2Columns)AND type in(NU)DROP TABLE dbo.TestRows2Columns G

3、O CREATE TABLE dbo.TestRows2Columns(Id int IDENTITY(1,1)NOT NULL,UserName nvarchar(50)NULL,Subject nvarchar(50)NULL,Source numeric(18,0)NULL)ON PRIMARY GO -插入测试数据 INSERT INTO TestRows2Columns(UserName,Subject,Source)SELECT N张三,N语文,60 UNION ALL SELECT N李四,N数学,70 UNION ALL SELECT N王五,N英语,80 UNION ALL

4、SELECT N王五,N数学,75 UNION ALL SELECT N王五,N语文,57 UNION ALL SELECT N李四,N语文,80 UNION ALL SELECT N张三,N英语,100 GO SELECT*FROM TestRows2Columns （图 2：样本数据）(二)先以静态的方式实现行转列，效果如图 3 所示：-1：静态拼接行转列 SELECT UserName,SUM(CASE Subject WHEN 数学 THEN Source ELSE 0 END)AS 数学,SUM(CASE Subject WHEN 英语 THEN Source ELSE 0 END)

5、AS 英语,SUM(CASE Subject WHEN 语文 THEN Source ELSE 0 END)AS 语文 FROM TestRows2Columns GROUP BY UserName GO （图 3：样本数据）(三)接着以动态的方式实现行转列，这是使用拼接 SQL 的方式实现的，所以它适用于 SQL Server 2000 以上的数据库版本，执行脚本返回的结果如图 2 所示；-2：动态拼接行转列 DECLARE sql VARCHAR(8000)SET sql=SELECT UserName,SELECT sql=sql+SUM(CASE Subject WHEN +Subje

6、ct+THEN Source ELSE 0 END)AS+QUOTENAME(Subject)+,FROM(SELECT DISTINCT Subject FROM TestRows2Columns)AS a SELECT sql=LEFT(sql,LEN(sql)-1)+FROM TestRows2Columns GROUP BY UserName PRINT(sql)EXEC(sql)GO (四)在 SQL Server 2005 之后有了一个专门的 PIVOT 和 UNPIVOT 关系运算符做行列之间的转换，下面是静态的方式实现的，实现效果如图 4 所示：-3：静态 PIVOT 行转列

7、SELECT *FROM (SELECT UserName,Subject,Source FROM TestRows2Columns )p PIVOT(SUM(Source)FOR Subject IN(数学,英语,语文)AS pvt ORDER BY pvt.UserName;GO （图 4）(五)把上面静态的 SQL 基础上进行修改，这样就不用理会记录里面存储了什么，需要转成什么列名的问题了，脚本如下，效果如图 4 所示：-4：动态 PIVOT 行转列 DECLARE sql_str VARCHAR(8000)DECLARE sql_col VARCHAR(8000)SELECT sql_

8、col=ISNULL(sql_col+,)+QUOTENAME(Subject)FROM TestRows2Columns GROUP BY Subject SET sql_str=SELECT*FROM(SELECT UserName,Subject,Source FROM TestRows2Columns)p PIVOT (SUM(Source)FOR Subject IN(+sql_col+)AS pvt ORDER BY pvt.UserName PRINT(sql_str)EXEC(sql_str)(六)也许很多人到了上面一步就够了，但是你会发现，当别人拿到你的代码，需要不断的修改成

9、他自己环境中表名、分组列、行转列字段、字段值这几个参数，逻辑如图 5 所示，所以，我继续对上面的脚本进行修改，你只要设置自己的参数就可以实现行转列了，效果如图 4 所示：-5：参数化动态 PIVOT 行转列-=-Author:-Create date:-Description:-Blog:-=DECLARE sql_str NVARCHAR(MAX)DECLARE sql_col NVARCHAR(MAX)DECLARE tableName SYSNAME-行转列表 DECLARE groupColumn SYSNAME-分组字段 DECLARE row2column SYSNAME-行变列的

10、字段 DECLARE row2columnValue SYSNAME-行变列值的字段 SET tableName=TestRows2Columns SET groupColumn=UserName SET row2column=Subject SET row2columnValue=Source -从行数据中获取可能存在的列 SET sql_str=N SELECT sql_col_out=ISNULL(sql_col_out+,)+QUOTENAME(+row2column+)FROM+tableName+GROUP BY+row2column+-PRINT sql_str EXEC sp_

11、executesql sql_str,Nsql_col_out NVARCHAR(MAX)OUTPUT,sql_col_out=sql_col OUTPUT-PRINT sql_col SET sql_str=N SELECT*FROM(SELECT+groupColumn+,+row2column+,+row2columnValue+FROM+tableName+)p PIVOT (SUM(+row2columnValue+)FOR+row2column+IN(+sql_col+)AS pvt ORDER BY pvt.+groupColumn+-PRINT(sql_str)EXEC(sql

12、_str)（图 5）(七)在实际的运用中，我经常遇到需要对基础表的数据进行筛选后再进行行转列，那么下面的脚本将满足你这个需求，效果如图 6 所示：-6：带条件查询的参数化动态 PIVOT 行转列-=-Author:-Create date:-Description:-Blog:-=DECLARE sql_str NVARCHAR(MAX)DECLARE sql_col NVARCHAR(MAX)DECLARE sql_where NVARCHAR(MAX)DECLARE tableName SYSNAME-行转列表 DECLARE groupColumn SYSNAME-分组字段 DECLAR

13、E row2column SYSNAME-行变列的字段 DECLARE row2columnValue SYSNAME-行变列值的字段 SET tableName=TestRows2Columns SET groupColumn=UserName SET row2column=Subject SET row2columnValue=Source SET sql_where=WHERE UserName=王五 -从行数据中获取可能存在的列 SET sql_str=N SELECT sql_col_out=ISNULL(sql_col_out+,)+QUOTENAME(+row2column+)F

14、ROM+tableName+sql_where+GROUP BY+row2column+-PRINT sql_str EXEC sp_executesql sql_str,Nsql_col_out NVARCHAR(MAX)OUTPUT,sql_col_out=sql_col OUTPUT-PRINT sql_col SET sql_str=N SELECT*FROM(SELECT+groupColumn+,+row2column+,+row2columnValue+FROM+tableName+sql_where+)p PIVOT (SUM(+row2columnValue+)FOR+row2column+IN(+sql_col+)AS pvt ORDER BY pvt.+groupColumn+-PRINT(sql_str)EXEC(sql_str)（图 6）