动态行转列小记

数据库环境:SQL SERVER 2005

  最近在整理数据时,要将查询到的数据张贴到Excel中。在Excel中,要展示的行数是固定的,列数不固定,

一个一个数据的复制张贴太烦人。想了一个偷懒的方法,直接将查询的数据进行行转列,达到和Excel中的效果一致,

再整列复制到Excel中。

1.创建测试表,导入测试数据

/*从系统表sysobjects取出name字段的42行数据,用作测试数据*/
WITH    x0
          AS ( SELECT TOP 42
                        name
               FROM     sysobjects
             ),/*产生序号*/
        x1
          AS ( SELECT   ROW_NUMBER() OVER ( ORDER BY name ) AS rn ,
                        name
               FROM     x0
             ),/*生成组号*/
        x2
          AS ( SELECT   ( rn - 1 ) / 5 AS gp ,--不固定列数
                        rn % 5 AS col ,--分5列
                        rn ,
                        name
               FROM     x1
             )
    SELECT  *
    INTO    #t
    FROM    x2
View Code

2.固定列数行转列实现

  在测试数据里,我们已经将数据分了固定5个组。因此,可以通过行转列将数据分成5列显示。

SELECT  *
FROM    ( SELECT    gp ,
                    col ,
                    name
          FROM      #t
        ) AS t1 PIVOT
( MAX(name) FOR col IN ( [0], [1], [2], [3], [4] ) ) AS t2
View Code

  看一下已知列数的分组结果

3.动态行转列

  现在我要把数据在5行中显示,至于要分多少列,我不管。

“动态”即事先不知道要转成多少列,所以,“动态行转列”首要解决的问题是知道要转多少列,解决了这个问题,

行转列的实现直接套用2中的实现方法。

DECLARE @sql VARCHAR(MAX)
DECLARE @sql_col VARCHAR(255)
SELECT  @sql_col = ISNULL(@sql_col + ',', '') + QUOTENAME([gp])
FROM    #t
GROUP BY gp

SET @sql = 'SELECT  *
    FROM    ( SELECT    gp ,
                        col ,
                        name
              FROM      #t
            ) AS t1 PIVOT
( MAX(name) FOR gp IN (' + @sql_col + ') ) AS t2'
EXEC (@sql)
View Code

  动态行转列的结果

在SQL SERVER 2005中实现动态行转列,我能想到的办法就是通过T-SQL来实现。

(本文完)

原文地址:https://www.cnblogs.com/boss-he/p/4740472.html