将行转换为列 sql server [英] Convert rows into columns sql server

问题描述

我想将行转换为列.

Column1 Column2    Column3     Column4  Column5 Column6 Column7
 1      2016-07-25  7           3        c1      c11    c111
 2      2016-07-26  5           2        c2      c22    c222
 3      2016-07-27  1           2        c3      c33    c333
 4      2016-07-28  3           1        c4      c44    c444

我想输出如下:

Column1        1            2         3           4
Column2    2016-07-25    2016-07-26  2016-07-27  2016-07-28
Column3        7            5         1           3
Column4        3            2         2           1
Column5       c1            c2       c3          c4
Column6       c11           c22      c33         c44
Column7       c111          c222     c333        c44

我尝试使用pivot unpivot 来实现，但没有找到合适的解决方案.第一个表(输入)可以有 n 行.

I tried to do it using pivot unpivot but did not find proper solution. First Table (input) can have n number of rows.

推荐答案

您需要使用 UNPIVOT，然后使用 PIVOT，如果列数未知 - 动态 SQL:

You need to use UNPIVOT, then PIVOT and if columns number is unknown - dynamic SQL:

DECLARE @columns nvarchar(max), 
        @columns_with_convert nvarchar(max),
        @row_numbers nvarchar(max),
        @sql nvarchar(max)

SELECT @columns = STUFF((
SELECT ','+QUOTENAME(name)
FROM sys.columns
WHERE [object_id] = OBJECT_ID('##YourTable')
FOR XML PATH('')),1,1,'')
--Will get you [Column1],[Column2],[Column3],[Column4],[Column5],[Column6],[Column7]

SELECT @columns_with_convert = (
SELECT 'CAST('+QUOTENAME(name)+' as nvarchar(max)) as '+QUOTENAME(name) +','
FROM sys.columns
WHERE [object_id] = OBJECT_ID('##YourTable')
FOR XML PATH(''))
--Will get you CAST([Column1] as nvarchar(max)) as [Column1], ... because all rows must be same datatype.

SELECT @row_numbers = STUFF((
SELECT ','+ QUOTENAME(CAST(ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as nvarchar(max)))
FROM ##YourTable
FOR XML PATH('')),1,1,'')
--This will be used when PIVOTing ([1],[2],[3],[4])

SELECT @sql = '
SELECT *
FROM (
    SELECT  RN, 
            [Columns],
            [Values]
    FROM (
        SELECT  '+@columns_with_convert+'
                ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as RN
        FROM ##YourTable
        ) as p
    UNPIVOT (
        [Values] FOR [Columns] IN ('+@columns+')
    ) as unpvt
) as s
PIVOT (
    MAX([Values]) FOR RN IN ('+@row_numbers+')
) as pvt
'

EXEC sp_executesql @sql

输出:

Columns 1           2           3           4
Column1 1           2           3           4
Column2 2016-07-25  2016-07-26  2016-07-27  2016-07-28
Column3 7           5           1           3
Column4 3           2           2           1
Column5 c1          c2          c3          c4
Column6 c11         c22         c33         c44
Column7 c111        c222        c333        c444

如果你PRINT @sql，你会得到一个查询文本:

If you PRINT @sql you will get a text of query:

SELECT *
FROM (
    SELECT  RN, 
            [Columns],
            [Values]
    FROM (
        SELECT  CAST([Column1] as nvarchar(max)) as [Column1],CAST([Column2] as nvarchar(max)) as [Column2],CAST([Column3] as nvarchar(max)) as [Column3],CAST([Column4] as nvarchar(max)) as [Column4],CAST([Column5] as nvarchar(max)) as [Column5],CAST([Column6] as nvarchar(max)) as [Column6],CAST([Column7] as nvarchar(max)) as [Column7],
                ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as RN
        FROM ##YourTable
        ) as p
    UNPIVOT (
        [Values] FOR [Columns] IN ([Column1],[Column2],[Column3],[Column4],[Column5],[Column6],[Column7])
    ) as unpvt
) as s
PIVOT (
    MAX([Values]) FOR RN IN ([1],[2],[3],[4])
) as pvt

这篇关于将行转换为列 sql server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！