首页 爱情文章 亲情文章 友情文章 生活随笔 校园文章 经典文章 人生哲理 励志文章 搞笑文章 心情日记
  • 爱情文章
  • 亲情文章
  • 友情文章
  • 生活随笔
  • 校园文章
  • 经典文章
  • 人生哲理
  • 励志文章
  • 搞笑文章
  • 心情日记
  • 英语文章
  • 范文大全
  • 作文大全
  • 新闻阅读
    •
    当前位置: 山茶花美文网 > 经典文章 > 正文

    【SQL,Server,动态行转列】oracle动态行转列

    时间：2020-05-28来源：山茶花美文网 本文已影响 人 山茶花美文网手机站

    一.本文所涉及的内容(Contents)

    本文所涉及的内容(Contents)

    背景(Contexts)

    实现代码(SQL Codes)

    方法一：使用拼接SQL，静态列字段;

    方法二：使用拼接SQL，动态列字段;

    方法三：使用PIVOT关系运算符，静态列字段;

    方法四：使用PIVOT关系运算符，动态列字段;

    二.背景(Contexts)

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

    SQL Server 动态行转列1

    (图1：行转列效果图)

    三.实现代码(SQL Codes)

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

    --创建测试表

    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]

    SQL Server 动态行转列2

    (图2：样本数据)

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

    --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

    SQL Server 动态行转列3

    (图3：样本数据)

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

    --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

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

    --3：静态PIVOT行转列

    SELECT *

    FROM ( SELECT [UserName] ,

    [Subject] ,

    [Source]

    FROM [TestRows2Columns]

    ) p PIVOT

    ( SUM([Source]) FOR [Subject] IN ( [数学],[英语],[语文] ) ) AS pvt

    ORDER BY pvt.[UserName];

    GO

    SQL Server 动态行转列4

    (图4)

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

    --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)

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

    --5：参数化动态PIVOT行转列

    -- =============================================

    -- Author: <听风吹雨>

    -- Create date: <2014.05.26>

    -- Description: <参数化动态PIVOT行转列>

    -- Blog:

    -- =============================================

    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)

    SQL Server 动态行转列5

    (图5)

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

    --6：带条件查询的参数化动态PIVOT行转列

    -- =============================================

    -- Author: <听风吹雨>

    -- Create date: <2014.05.26>

    -- Description: <参数化动态PIVOT行转列，带条件查询的参数化动态PIVOT行转列>

    -- Blog:

    -- =============================================

    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)

    SQL Server 动态行转列6

    (图6)

    • 【SQL,Server,动态行转列】oracle动态行转列 相关文章:
    • 爱情文章
    • 亲情文章
    • 友情文章
    • 随笔
    • 哲理
    • 励志
    • 范文大全

    最新文章

    热点文章

    推荐访问

    军训远足心得体会 军训爬山心得体会 大学军训徒步拉练感想 军训徒步作文 军训徒步10公里感想 军训徒步心得体会 5分钟演讲中秋节节日 中秋一分钟演讲 中秋节演讲稿4分钟 中秋节演讲稿一分钟 中秋节演讲稿五分钟 3-5分钟 中秋节短篇演讲稿 中秋节演讲稿两分钟 中秋节的演讲稿3分钟 关于中秋节的演讲稿三分钟 孩子体验军训的心得 参加孩子军训活动的感受与收获 家长对孩子军训后的收获和感悟 小孩军训的收获与感悟 学生军训的收获与感悟 孩子军训成长过程家长感悟 孩子军训感悟 孩子军训心得体会 孩子军训的收获与感悟 兵役前训练心得体会 部队当兵的心得 新兵役前教育训练心得体会 预备役军训心得体会 新兵军训感悟

    本文【SQL,Server,动态行转列】oracle动态行转列由山茶花美文网整理提供，版权归原作者、原出处所有。

    Copyright © 山茶花美文网 All Rights Reserved.