SQL Server - PIVOT - 两列转换为行

Question

我在单个列中看到了很多有关 PIVOT 的问题，每个问题都比其他问题更复杂，但是，我找不到我需要的任何内容。

说实话，我什至不知道pivot在这种情况下是否能帮助我。

假设我的源表中有以下数据:

SELECT '1' as 'RowId', 'RandomName1' as 'First', 'RandomLast1' as 'Last'
UNION
SELECT '2' as 'RowId', 'RandomName2' as 'First', 'RandomLast2' as 'Last'
UNION
SELECT '3' as 'RowId', 'RandomName3' as 'First', 'RandomLast3' as 'Last'
UNION
SELECT '4' as 'RowId', 'RandomName4' as 'First', 'RandomLast4' as 'Last'
UNION
SELECT '5' as 'RowId', 'RandomName5' as 'First', 'RandomLast5' as 'Last'

最多 5 行包含名字和姓氏。First 和 Last 列的值是随机的。

RowId First       Last
----- ----------- -----------
1     RandomName1 RandomLast1
2     RandomName2 RandomLast2
3     RandomName3 RandomLast3
4     RandomName4 RandomLast4
5     RandomName5 RandomLast5

我试图将这些数据转换为这样的内容:

First1      Last1       First2      Last2       First3      Last3       First4      Last4       First5      Last5
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
RandomName1 RandomLast1 RandomName2 RandomLast2 RandomName3 RandomLast3 RandomName4 RandomLast4 RandomName5 RandomLast5

例如:如果列 First5 和 Last5 为 NULL，我不会有任何问题，因为只有 4 行。

First1      Last1       First2      Last2       First3      Last3       First4      Last4       First5      Last5
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
RandomName1 RandomLast1 RandomName2 RandomLast2 RandomName3 RandomLast3 RandomName4 RandomLast4 NULL        NULL

谁能给我一点帮助吗？谢谢。

<小时/>

基于 Sheela K R 答案的解决方案:

SELECT 
    MAX(First1) as 'First1',  MAX(Last1) as 'Last1',
    MAX(First2) as 'First2',  MAX(Last2) as 'Last2',
    MAX(First3) as 'First3',  MAX(Last3) as 'Last3',
    MAX(First4) as 'First4',  MAX(Last4) as 'Last4',
    MAX(First5) as 'First5',  MAX(Last5) as 'Last5'
FROM
(
    SELECT 
        CASE WHEN RowId = 1 THEN [First] END as 'First1',
        CASE WHEN RowId = 1 THEN [Last] END as 'Last1',
        CASE WHEN RowId = 2 THEN [First] END as 'First2',
        CASE WHEN RowId = 2 THEN [Last] END as 'Last2',
        CASE WHEN RowId = 3 THEN [First] END as 'First3',
        CASE WHEN RowId = 3 THEN [Last] END as 'Last3',
        CASE WHEN RowId = 4 THEN [First] END as 'First4',
        CASE WHEN RowId = 4 THEN [Last] END as 'Last4',
        CASE WHEN RowId = 5 THEN [First] END as 'First5',
        CASE WHEN RowId = 5 THEN [Last] END as 'Last5'
    FROM
    (
        SELECT '1' as 'RowId', 'RandomName1' as 'First', 'RandomLast1' as 'Last'
        UNION SELECT '2' as 'RowId', 'RandomName2' as 'First', 'RandomLast2' as 'Last'
        UNION SELECT '3' as 'RowId', 'RandomName3' as 'First', 'RandomLast3' as 'Last'
        UNION SELECT '4' as 'RowId', 'RandomName4' as 'First', 'RandomLast4' as 'Last'
        --UNION SELECT '5' as 'RowId', 'RandomName5' as 'First', 'RandomLast5' as 'Last'
    ) test
) test2

Answer 1

有几种不同的方法可以获得您想要的结果。类似于 @Sheela K R's答案你可以使用带有 CASE 表达式的聚合函数，但它可以以更简洁的方式编写:

select 
  max(case when rowid = 1 then first end) First1,
  max(case when rowid = 1 then last end) Last1,
  max(case when rowid = 2 then first end) First2,
  max(case when rowid = 2 then last end) Last2,
  max(case when rowid = 3 then first end) First3,
  max(case when rowid = 3 then last end) Last3,
  max(case when rowid = 4 then first end) First4,
  max(case when rowid = 4 then last end) Last4,
  max(case when rowid = 5 then first end) First5,
  max(case when rowid = 5 then last end) Last5
from yourtable;

参见SQL Fiddle with Demo .

这也可以使用 PIVOT 函数编写，但是由于您想要旋转多个列，那么您首先需要查看取消旋转您的 First 和 Last 列。

逆透视过程会将多列转换为多行数据。您没有指定所使用的 SQL Server 版本，但可以将 SELECT 与 UNION ALL 和 CROSS APPLY 一起使用，甚至可以使用 >UNPIVOT 函数执行第一次转换:

select col = col + cast(rowid as varchar(10)), value
from yourtable
cross apply 
(
  select 'First', First union all
  select 'Last', Last
) c (col, value)

参见SQL Fiddle with Demo 。这会将您的数据转换为以下格式:

|    COL |       VALUE |
|--------|-------------|
| First1 | RandomName1 |
|  Last1 | RandomLast1 |
| First2 | RandomName2 |
|  Last2 | RandomLast2 |

一旦数据位于多行中，您就可以轻松应用 PIVOT 函数:

select First1, Last1, 
  First2, Last2,
  First3, Last3, 
  First4, Last4, 
  First5, Last5
from
(
  select col = col + cast(rowid as varchar(10)), value
  from yourtable
  cross apply 
  (
    select 'First', First union all
    select 'Last', Last
  ) c (col, value)
) d
pivot
(
  max(value)
  for col in (First1, Last1, First2, Last2,
              First3, Last3, First4, Last4, First5, Last5)
) piv;

参见SQL Fiddle with Demo

两者给出的结果是:

|      FIRST1 |       LAST1 |      FIRST2 |       LAST2 |      FIRST3 |       LAST3 |      FIRST4 |       LAST4 |      FIRST5 |       LAST5 |
|-------------|-------------|-------------|-------------|-------------|-------------|-------------|-------------|-------------|-------------|
| RandomName1 | RandomLast1 | RandomName2 | RandomLast2 | RandomName3 | RandomLast3 | RandomName4 | RandomLast4 | RandomName5 | RandomLast5 |