SQL Server 动态行转列（参数化表名、分组列、行转列字段、字段值）

旧城等待， 2021-12-24 04:55 361阅读 0赞

# **一.本文所涉及的内容（Contents）** #

1.  [本文所涉及的内容（Contents）][Contents]
2.  [背景（Contexts）][Contexts]
3.  [实现代码（SQL Codes）][SQL Codes]
    
    1.  [方法一：使用拼接SQL，静态列字段；][SQL]
    2.  [方法二：使用拼接SQL，**动态**列字段；][SQL 1]
    3.  [方法三：使用PIVOT关系运算符，静态列字段；][PIVOT]
    4.  [方法四：使用PIVOT关系运算符，**动态**列字段；][PIVOT 1]
    5.  [扩展阅读一：参数化表名、分组列、行转列字段、字段值；][Link 1]
    6.  [扩展阅读二：在前面的基础上加入条件过滤；][Link 2]
4.  [参考文献（References）][References]

# **二.背景（Contexts）** #

其实行转列并不是一个什么新鲜的话题了，甚至已经被大家说到烂了，网上的很多例子多多少少都有些问题，所以我希望能让大家快速的看到执行的效果，所以在动态列的基础上再把表、分组字段、行转列字段、值这四个行转列固定需要的值变成真正意义的参数化，大家只需要根据自己的环境，设置参数值，马上就能看到效果了（可以直接跳转至：“[参数化动态PIVOT行转列][Link 1]”查看具体的脚本代码）。行转列的效果图如图1所示：

[![wps\_clip\_image-32543][wps_clip_image-32543]][wps_clip_image-32543_wps_clip_image-32543]

（图1：行转列效果图）

# **三.实现代码（SQL Codes）** #

(一) 首先我们先创建一个测试表，往里面插入测试数据，返回表记录如图2所示：

[![复制代码][copycode.gif]][copycode.gif 1]

--创建测试表
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestRows2Columns]') AND type in (N'U'))
    DROP TABLE [dbo].[TestRows2Columns]
    GO
    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
        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]

[![复制代码][copycode.gif]][copycode.gif 1]

[![wps\_clip\_image-8842][wps_clip_image-8842]][wps_clip_image-8842_wps_clip_image-8842]

（图2：样本数据）

(二) 先以静态的方式实现行转列，效果如图3所示：

[![复制代码][copycode.gif]][copycode.gif 1]

--1：静态拼接行转列
    SELECT [UserName],
    SUM(CASE [Subject] WHEN '数学' THEN [Source] ELSE 0 END) AS '[数学]',
    SUM(CASE [Subject] WHEN '英语' THEN [Source] ELSE 0 END) AS '[英语]',
    SUM(CASE [Subject] WHEN '语文' THEN [Source] ELSE 0 END) AS '[语文]'     
    FROM [TestRows2Columns]
    GROUP BY [UserName]
    GO

[![复制代码][copycode.gif]][copycode.gif 1]

[![wps\_clip\_image-14456][wps_clip_image-14456]][wps_clip_image-14456_wps_clip_image-14456]

（图3：样本数据）

(三) 接着以动态的方式实现行转列，这是使用拼接SQL的方式实现的，所以它适用于SQL Server 2000以上的数据库版本，执行脚本返回的结果如图2所示；

[![复制代码][copycode.gif]][copycode.gif 1]

--2：动态拼接行转列
    DECLARE @sql VARCHAR(8000)
    SET @sql = 'SELECT [UserName],'   
    SELECT @sql = @sql + 'SUM(CASE [Subject] WHEN '''+[Subject]+''' 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

[![复制代码][copycode.gif]][copycode.gif 1]

(四) 在SQL Server 2005之后有了一个专门的PIVOT 和 UNPIVOT 关系运算符做行列之间的转换，下面是静态的方式实现的，实现效果如图4所示：

[![复制代码][copycode.gif]][copycode.gif 1]

--3：静态PIVOT行转列
    SELECT  *
    FROM    ( SELECT    [UserName] ,
                        [Subject] ,
                        [Source]
              FROM      [TestRows2Columns]
            ) p PIVOT
    ( SUM([Source]) FOR [Subject] IN ( [数学],[英语],[语文] ) ) AS pvt
    ORDER BY pvt.[UserName];
    GO

[![复制代码][copycode.gif]][copycode.gif 1]

[![wps\_clip\_image-23886][wps_clip_image-23886]][wps_clip_image-23886_wps_clip_image-23886]

（图4）

(五) 把上面静态的SQL基础上进行修改，这样就不用理会记录里面存储了什么，需要转成什么列名的问题了，脚本如下，效果如图4所示：

[![复制代码][copycode.gif]][copycode.gif 1]

--4：动态PIVOT行转列
    DECLARE @sql_str VARCHAR(8000)
    DECLARE @sql_col VARCHAR(8000)
    SELECT @sql_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)

[![复制代码][copycode.gif]][copycode.gif 1]

(六) 也许很多人到了上面一步就够了，但是你会发现，当别人拿到你的代码，需要不断的修改成他自己环境中表名、分组列、行转列字段、字段值这几个参数，逻辑如图5所示，所以，我继续对上面的脚本进行修改，你只要设置自己的参数就可以实现行转列了，效果如图4所示：

[![复制代码][copycode.gif]][copycode.gif 1]

--5：参数化动态PIVOT行转列
    -- =============================================
    -- Author:        <听风吹雨>
    -- Create date: <2014.05.26>
    -- Description:    <参数化动态PIVOT行转列>
    -- Blog:        <http://www.cnblogs.com/gaizai/>
    -- =============================================
    DECLARE @sql_str NVARCHAR(MAX)
    DECLARE @sql_col NVARCHAR(MAX)
    DECLARE @tableName SYSNAME --行转列表
    DECLARE @groupColumn SYSNAME --分组字段
    DECLARE @row2column SYSNAME --行变列的字段
    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_executesql @sql_str,N'@sql_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_str)

[![复制代码][copycode.gif]][copycode.gif 1]

[![wps\_clip\_image-17757][wps_clip_image-17757]][wps_clip_image-17757_wps_clip_image-17757]

（图5）

(七) 在实际的运用中，我经常遇到需要对基础表的数据进行筛选后再进行行转列，那么下面的脚本将满足你这个需求，效果如图6所示：

[![复制代码][copycode.gif]][copycode.gif 1]

--6：带条件查询的参数化动态PIVOT行转列
    -- =============================================
    -- Author:        <听风吹雨>
    -- Create date: <2014.05.26>
    -- Description:    <参数化动态PIVOT行转列，带条件查询的参数化动态PIVOT行转列>
    -- Blog:        <http://www.cnblogs.com/gaizai/>
    -- =============================================
    DECLARE @sql_str NVARCHAR(MAX)
    DECLARE @sql_col NVARCHAR(MAX)
    DECLARE @sql_where NVARCHAR(MAX)
    DECLARE @tableName SYSNAME --行转列表
    DECLARE @groupColumn SYSNAME --分组字段
    DECLARE @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+']) 
        FROM ['+@tableName+'] '+@sql_where+' GROUP BY ['+@row2column+']'
    --PRINT @sql_str
    EXEC sp_executesql @sql_str,N'@sql_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)

[![复制代码][copycode.gif]][copycode.gif 1]

[![wps\_clip\_image-10798][wps_clip_image-10798]][wps_clip_image-10798_wps_clip_image-10798]

（图6）

# **四.参考文献（References）** #

[使用 PIVOT 和 UNPIVOT][PIVOT _ UNPIVOT]

作者：[听风吹雨][Link 3]   
出处：[ http://www.cnblogs.com/gaizai/][Link 3]   
邮箱：[gaizai@126.com][gaizai_126.com]

转载于:https://www.cnblogs.com/kevinGao/p/4188738.html

[Contents]: /images/20211223/e4521ad72412478aba169dd51800f4d3.png#_labelContents
[Contexts]: /images/20211223/e4521ad72412478aba169dd51800f4d3.png#_labelContexts
[SQL Codes]: /images/20211223/e4521ad72412478aba169dd51800f4d3.png#_labelSQLCodes
[SQL]: /images/20211223/e4521ad72412478aba169dd51800f4d3.png#_labelOne
[SQL 1]: /images/20211223/e4521ad72412478aba169dd51800f4d3.png#_labelTwo
[PIVOT]: /images/20211223/e4521ad72412478aba169dd51800f4d3.png#_labelThree
[PIVOT 1]: /images/20211223/e4521ad72412478aba169dd51800f4d3.png#_labelFour
[Link 1]: /images/20211223/e4521ad72412478aba169dd51800f4d3.png#_labelFive
[Link 2]: /images/20211223/e4521ad72412478aba169dd51800f4d3.png#_labelSix
[References]: /images/20211223/e4521ad72412478aba169dd51800f4d3.png#_labelReferences
[wps_clip_image-32543]: https://images0.cnblogs.com/blog/48305/201405/261609027757993.png
[wps_clip_image-32543_wps_clip_image-32543]: /images/20211223/17a6b9825c8b43e8b5824e0040311813.png
[copycode.gif]: https://common.cnblogs.com/images/copycode.gif
[copycode.gif 1]: /images/20211223/e4521ad72412478aba169dd51800f4d3.png
[wps_clip_image-8842]: https://images0.cnblogs.com/blog/48305/201405/261609036663322.png
[wps_clip_image-8842_wps_clip_image-8842]: /images/20211223/08bc34f5566d465eb9e46d4d68355919.png
[wps_clip_image-14456]: https://images0.cnblogs.com/blog/48305/201405/261609042754735.png
[wps_clip_image-14456_wps_clip_image-14456]: /images/20211223/d79869898d1b41618db2d41147ad9cf0.png
[wps_clip_image-23886]: https://images0.cnblogs.com/blog/48305/201405/261609062441635.png
[wps_clip_image-23886_wps_clip_image-23886]: /images/20211223/ac3b7d6e6be04018961a1b82a57019f7.png
[wps_clip_image-17757]: https://images0.cnblogs.com/blog/48305/201405/261609075095148.png
[wps_clip_image-17757_wps_clip_image-17757]: /images/20211223/5e77bc86eb624a26bc4a6238c628f2ea.png
[wps_clip_image-10798]: https://images0.cnblogs.com/blog/48305/201405/261609108064373.png
[wps_clip_image-10798_wps_clip_image-10798]: /images/20211223/564be827eede4677b16ad28b19302701.png
[PIVOT _ UNPIVOT]: http://technet.microsoft.com/zh-cn/library/ms177410%28v=sql.105%29.aspx
[Link 3]: http://www.cnblogs.com/gaizai/
[gaizai_126.com]: mailto:Gaizai@126.com

发表评论取消回复

表情：

评论列表（有 0 条评论，361人围观）

还没有评论，来说两句吧...

相关阅读

相关 MySQL 字段值按照分隔符拆分成列（行转列）

拆分前： ![3cd1a5bb3d8742d891b15d585513dfb3.png][] 拆分后： ![8beff249096c4445a5d730d9a9d01b3

迈不过友情╰/ 2024年04月08日 09:48/ 0 赞/ 90 阅读

相关 sql如何行转列，列转行

我们都知道在sql查询中，union 是增行，join是增列，但是如果碰到需要行转列，列转行时，一味的使用 join来完成则会变的异常繁杂。如把一天24小时的行转换成列，则需要

逃离我推掉我的手/ 2023年06月22日 13:48/ 0 赞/ 29 阅读

相关 SQL 行转列和列转行

行转列，列转行是我们在开发过程中经常碰到的问题。行转列一般通过CASE WHEN 语句来实现，也可以通过 SQL SERVER 2005 新增的运算符PIVOT来实现。用传统的

骑猪看日落/ 2022年06月16日 12:47/ 0 赞/ 427 阅读

相关 sql 行转列

[SQL行转列汇总][SQL] PIVOT 用于将列值旋转为列名（即行转列），在 SQL Server 2000可以用聚合函数配合CASE语句实现 PIVOT 的一般语

骑猪看日落/ 2022年06月12日 02:42/ 0 赞/ 384 阅读

相关 SQL 行转列列转行

一，一列多行转一行，逗号分隔 ![Center][] SQL : select stuff((select ','+id from Table\_1 fo

「爱情、让人受尽委屈。」/ 2022年06月07日 23:55/ 0 赞/ 366 阅读

相关 sql 行转列

原数据： select \ from GZWZ\_BZMCZJDGS t ![70][] 注意： \-- to\_char() : 转换成字符，在使用lis

迈不过友情╰/ 2022年05月22日 03:34/ 0 赞/ 250 阅读

相关 sql行转列、列转行

-- 建表 CREATE TABLE StudentScores ( UserName NVARCHAR(20),

- 日理万妓/ 2022年05月17日 03:11/ 0 赞/ 422 阅读

相关 SQL Server 动态行转列（参数化表名、分组列、行转列字段、字段值）

一.本文所涉及的内容（Contents） 1. [本文所涉及的内容（Contents）][Contents] 2. [背景（Contexts）][Conte

旧城等待，/ 2021年12月24日 04:55/ 0 赞/ 362 阅读

相关 Sql Server 行转列

SQL Server 行转列聚合函数的选择： [http://www.cnblogs.com/wlsandwho/p/4423956.html][http_www.cnblo

朴灿烈づ我的快乐病毒、/ 2021年12月15日 13:55/ 0 赞/ 355 阅读

相关 SQL Server 行转列，列转行。多行转成一列

一、多行转成一列（并以","隔开）表名：A 表数据： ![680322-20170214153055238-1343550028.png][] 想要的查询结果： !

Love The Way You Lie/ 2021年12月11日 07:55/ 0 赞/ 676 阅读