SQL Server - PIVOT - 两列成行 [英] SQL Server - PIVOT - two columns into rows
问题描述
我在一个专栏中看到了很多关于 PIVOT 的问题，每个问题都比其他问题更复杂，但是，我找不到任何我需要的东西.
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.
假设我的源表中有这些数据:
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'
最多 5 行，包括名字和姓氏.First 和 Last 列的值将是随机的.
Maximum of 5 rows with the first name and last name. The value of the columns First and Last will be random.
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
例如:如果列 First5 和 Last5 为 NULL，我没有任何问题，因为只有 4 行.
For example: I don't have any problem if columns First5 and Last5 are NULL because there are only 4 rows.
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.
基于 Sheela K R 答案的解决方案:
Solution based on Sheela K R answers:
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
推荐答案
有几种不同的方法可以获得您想要的结果.类似于 @Sheela K R's 的答案，您可以使用带有 CASE 表达式的聚合函数，但可以用更简洁的方式编写方式:
There are a few different ways that you can get the result that you want. Similar to @Sheela K R's answer you can use an aggregate function with a CASE expression but it can be written in a more concise way:
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;
这也可以使用 PIVOT 函数编写，但是由于您想要旋转多个列，那么您首先需要查看取消旋转您的 First 和 Last 列.
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 First and Last columns.
逆透视过程会将您的多列转换为多行数据.您没有指定您使用的 SQL Server 版本，但您可以使用 SELECT 和 UNION ALL 和 CROSS APPLY 甚至 >UNPIVOT 函数执行第一次转换:
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 SELECT with UNION ALL with CROSS APPLY or even the UNPIVOT function to perform the first conversion:
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.这会将您的数据转换为以下格式:
See SQL Fiddle with Demo. This converts your data into the format:
| COL | VALUE |
|--------|-------------|
| First1 | RandomName1 |
| Last1 | RandomLast1 |
| First2 | RandomName2 |
| Last2 | RandomLast2 |
一旦数据在多行中，您就可以轻松应用 PIVOT 功能:
Once the data is in multiple rows, then you can easily apply the PIVOT function:
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;
两者都给出了以下结果:
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 |
这篇关于SQL Server - PIVOT - 两列成行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
