SQL Server - PIVOT - 两列成行

SQL Server - PIVOT - two columns into rows

提问by navossoc

I saw many questions about PIVOT for a single column, each question more complex than other, however, I could not find anything like what I need.

我在一个专栏中看到了很多关于 PIVOT 的问题，每个问题都比其他问题更复杂，但是，我找不到任何我需要的东西。

To be honest, I don't even know if pivot will help me in this situation.

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

Let's say I have this data on my source table:

假设我的源表中有这些数据：

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'

Maximum of 5 rows with the first name and last name. The value of the columns First and Last will be random.

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

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

I was trying to pivot this data to something like this:

我试图将这些数据转变成这样的：

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

For example: I don't have any problem if columns First5 and Last5 are NULL because there are only 4 rows.

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

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

Can anyone give me a little help? Thanks.

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

---

---

Solution based on Sheela K R answers:

基于 Sheela KR 答案的解决方案：

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

回答by Taryn

There are a few different ways that you can get the result that you want. Similar to @Sheela K R'sanswer you can use an aggregate function with a CASE expression but it can be written in a more concise way:

有几种不同的方法可以获得您想要的结果。与@Sheela K R 的回答类似，您可以将聚合函数与 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;

See SQL Fiddle with Demo.

请参阅SQL Fiddle with Demo。

This could also be written using the PIVOT function, however since you want to pivot multiple columns then you would first want to look at unpivoting your Firstand Lastcolumns.

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

The unpivot process will convert your multiple columns into multiple rows of data. You did not specify what version of SQL Server you are using but you can use a SELECTwith UNION ALLwith CROSS APPLYor even the UNPIVOTfunction to perform the first conversion:

逆透视过程会将您的多列转换为多行数据。您没有指定您所使用的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)

See SQL Fiddle with Demo. This converts your data into the format:

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

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

Once the data is in multiple rows, then you can easily apply the PIVOT function:

一旦数据位于多行中，您就可以轻松应用 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;

See SQL Fiddle with Demo

参见SQL Fiddle with Demo

Both give a result of:

两者都给出了以下结果：

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

回答by Sheela K R

Try something like this

尝试这样的事情

CREATE TABLE #Table1
    ([uid] int, [name] varchar(4), [diseaseid] int, [intensity] varchar(4))
;

INSERT INTO #Table1
    ([uid], [name], [diseaseid], [intensity])
VALUES    (1, 'xxxx', 2, 'low')
    (1, 'xxxx', 1, 'high'),

;

SELECT MAX([uid]) AS [uid]
       ,MAX([name]) AS [name]
       ,MAX([diseaseid1]) AS [diseaseid1]
       ,MAX([intensity1]) AS [intensity1]
       ,MAX([diseaseid2]) AS [diseaseid2]
       ,MAX([intensity2]) [intensity2]
FROM 
(
    SELECT [uid], [name]
    , CASE WHEN rn=2 THEN NULL ELSE [diseaseid] END AS [diseaseid1]
    , CASE WHEN rn=2 THEN NULL ELSE [intensity] END AS [intensity1]
    , CASE WHEN rn=1 THEN NULL ELSE [diseaseid] END AS [diseaseid2]
    , CASE WHEN rn=1 THEN NULL ELSE [intensity] END AS [intensity2]
    FROM
    (
        SELECT [uid], [name], [diseaseid], [intensity], 
        ROW_NUMBER() OVER(PARTITION BY [uid] ORDER BY Name) AS rn
        FROM #Table1
    ) T
) T
GROUP BY [uid], [name]