如何在SQL-SERVER中使用PIVOT将多行数据转换为多列？

I am looking for an efficient way to convert rows to columns in SQL server using Pivot. I already got the output using aggregate function with cases and joins separately but I am hoping that PIVOT is a clear way to deal with this kind of rows to column transformation.

我正在寻找一种有效的方法来使用Pivot将行转换为SQL Server中的列。我已经使用聚合函数分别使用了case和join来获得输出,但我希望PIVOT是处理这种行到列转换的明确方法。

The example data is

示例数据是

-------------------------------------
| ID | Row | First |Last | Postal |
------------------------------------
| 1  | 1   | John  | Doe | B4K    |
| 1  | 2   | Matt  | Kev | 2H1    |
| 2  | 1   | Hary  | Lot | L26    |
| 2  | 2   | Fork  | Har | M3R    |
| 3  | 1   | Yuv   | Hal | L39    |
------------------------------------

This is my result:

这是我的结果:

| ID | First1 |Last1 | Postal1 | First2 |Last2 | Postal2 |
 -----------------------------------------------------------
| 1  | John   | Doe  | B4K     | Matt   | Kev  | 2H1     |
| 2  | Hary   | Lot  | L26     | Fork   | Har  | M3R     |
| 3  | Yuv    | Hal  | L39     | NULL   | NULL | NULL    |
-----------------------------------------------------------

How could I achieve this result using PIVOT?

我怎样才能使用PIVOT实现这个结果?

1 个解决方案

#1

Your data:

CREATE TABLE #Test (
    ID          INT,
    [Row]       INT,
    [First]     VARCHAR(32),
    [Last]      VARCHAR(32),
    [Postal]    VARCHAR(32)
)

INSERT #Test
VALUES
(1, 1, 'John', 'Doe', 'B4K'),
(1, 2, 'Matt', 'Kev', '2H1'),
(2, 1, 'Hary', 'Lot', 'L26'),
(2, 2, 'Fork', 'Har', 'M3R'),
(3, 1, 'Yuv', 'Hal', 'L39')

To get your expected result you have to use UNPIVOT before using PIVOT like below:

要获得预期的结果,您必须在使用PIVOT之前使用UNPIVOT,如下所示:

SELECT *
FROM (
    SELECT ID, ITEM + CAST([Row] AS VARCHAR) AS Name, VALUE
    FROM (
        SELECT *
        FROM #Test
    )s
    UNPIVOT
    (VALUE FOR Item IN ([First], [Last], [Postal])) p
) src
PIVOT
(MAX(VALUE) FOR Name IN ([First1], [Last1], [Postal1], [First2], [Last2], [Postal2])
) pvt

Obviously, the line (MAX(VALUE) FOR Name IN ([First1], [Last1], [Postal1], [First2], [Last2], [Postal2]) depends on count of different values in the field [Row] so the query has to be created dynamically.

显然,行(MAX(VALUE)FOR Name IN([First1],[Last1],[Postal1],[First2],[Last2],[Postal2])取决于字段[Row]中不同值的计数,所以必须动态创建查询。

Instead of using UNPIVOT/PIVOT you could create dynamically a query which will use OUTER APPLY:

您可以动态创建一个使用OUTER APPLY的查询,而不是使用UNPIVOT / PIVOT:

SELECT DISTINCT ID,
    t1.[First]  AS First1,
    t1.[Last]   AS Last1,
    t1.Postal   AS Postal1,
    t2.[First]  AS First2,
    t2.[Last]   AS Last2,
    t2.Postal   AS Postal2
FROM #Test t

    OUTER APPLY (
        SELECT TOP 1 [First], [Last], [Postal]
        FROM #Test
        WHERE ID = t.ID
            AND [Row] = 1
        ) t1

    OUTER APPLY (
        SELECT TOP 1 [First], [Last], [Postal]
        FROM #Test
        WHERE ID = t.ID
            AND [Row] = 2
        ) t2

#1