T-SQL将列标题转换为行

debugcn 发表于 Dev

库纳帕雷迪

我想换桌子。我有一个简单的“人”表，如下所示。

+---+----------+------------+------------+----------------------+
|ID | Person   | BirthDate  | Phone      | Email                |
+---+----------+------------+------------+----------------------+
| 1 | Tom      | 1985-11-08 | 1111111111 | [email protected]   |
+---+----------+------------+------------+----------------------+
| 2 | Dick     | 1982-02-24 | 2222222222 | [email protected] |
+---+----------+------------+------------+----------------------+
| 3 | Harry    | 1986-04-17 | 3333333333 | [email protected] |
+---+----------+------------+------------+----------------------+

我希望这张桌子可以像下面这样转置。

+-----------+--------------------+----------------------+----------------------+
| Key       | Value1             | Value2               | Value3               |
+-----------+--------------------+----------------------+----------------------+
| ID        | 1                  | 2                    | 3                    |
+-----------+--------------------+----------------------+----------------------+
| Person    | Tom                | Dick                 | Harry                |
+-----------+--------------------+----------------------+----------------------+
| BirthDate | 1985-11-08         | 1982-02-24           | 1986-04-17           |
+-----------+--------------------+----------------------+----------------------+
| Phone     | 1111111111         | 2222222222           | 3333333333           |
+-----------+--------------------+----------------------+----------------------+
| Email     | [email protected] | [email protected] | [email protected] |
+-----------+--------------------+----------------------+----------------------+

我正在使用MS SQL Server 2008 R2。

普雷迪普

试试这个。.首先，您需要unpivot使用列Cross apply来获取单行中的数据。然后pivot那一行得到结果。

CREATE TABLE #tt
  (ID        INT,Person    VARCHAR(50),BirthDate DATE,
     Phone     BIGINT,Email     VARCHAR(50)
  )

INSERT INTO #tt
VALUES      (1,'Tom','1985-11-08',1111111111,'[email protected]' ),
            ( 2,'Dick','1982-02-24',2222222222,'[email protected]'),
            ( 3,'Harry ','1986-04-17',3333333333,'[email protected]' ) 

SELECT [key],
       Max([value1]) [value1],
       Max([value2]) [value2],
       Max([value3]) [value3]
FROM   (SELECT 'value' + CONVERT(VARCHAR(30), id) valued,
               *
        FROM   #tt
               CROSS apply (VALUES ('ID',
                           CONVERT(VARCHAR(50), ID)),
                                   ('Person',Person),
                                   ('BirthDate',CONVERT(VARCHAR(50), BirthDate)),
                                   ('Phone',CONVERT(VARCHAR(50), Phone)),
                                   ('Email',Email)) cp ([key], data))a
       PIVOT (Max(data)
             FOR valued IN([value1],[value2],[value3])) piv
GROUP  BY [key]

动态版本

Declare @cols varchar(max)='',@aggcols varchar(max)='',@sql nvarchar(max)

SELECT @cols+= ',value' + CONVERT(VARCHAR(30), id) 
        FROM   #tt
SELECT @aggcols+= ',max([value' + CONVERT(VARCHAR(30), id) +']) value' + CONVERT(VARCHAR(30), id) 
        FROM   #tt
select @cols=  right(@cols,LEN(@cols)-1)
select @aggcols =right(@aggcols,LEN(@aggcols)-1)


set @sql = 'SELECT [key],
       '+@aggcols+'
FROM   (SELECT ''value'' + CONVERT(VARCHAR(30), id) valued,
               *
        FROM   #tt
               CROSS apply (VALUES (''ID'',CONVERT(VARCHAR(50), ID)),
                                   (''Person'',Person),
                                   (''BirthDate'',CONVERT(VARCHAR(50), BirthDate)),
                                   (''Phone'',CONVERT(VARCHAR(50), Phone)),
                                   (''Email'',Email)) cp ([key], data))a
       PIVOT (Max(data)
             FOR valued IN('+@cols+')) piv
GROUP  BY [key] '

execute sp_executesql @sql

输出

+----------+--------------------+---------------------+----------------------+
|key       |    value1          |   value2            |     value3           |
+----------+--------------------+---------------------+----------------------+
|BirthDate |    1985-11-08      |   1982-02-24        |     1986-04-17       |
+----------+--------------------+---------------------+----------------------+
|Email     | [email protected] |[email protected] | [email protected] |
+----------+--------------------+---------------------+----------------------+
|ID        |    1               |   2                 |     3                |
+----------+--------------------+---------------------+----------------------+
|Person    |    Tom             |   Dick              |     Harry            |
+----------+--------------------+---------------------+----------------------+
|Phone     |    1111111111      |   2222222222        |     3333333333       |
+----------+--------------------+---------------------+----------------------+

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。