• 时事博文
  • 文化时尚
  • 潮流娱乐
  • 生活常识
  • 健康生活
  • 旅游攻略
  • 体育风云
  • 财经博文
  • 汽车频道
  • 科技
  • 游戏
  • 女人
  • 互联网
  • 军事博览
  • 个性推荐
  • 当前位置: 精彩博文网 > 财经博文 > 正文

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

    时间:2018-08-15 16:35:26 来源:精彩博文网 本文已影响 精彩博文网手机站

    一.本文所涉及的内容(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)

    • 时事博文
    • 文化时尚
    • 潮流娱乐
    • 科技
    • 游戏
    • 女人
    • 个性推荐