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.
说实话，在这种情况下，我什至不知道枢轴是否对我有帮助.
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个带有名字和姓氏的行. 第一"和最后"列的值将是随机的.
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;
请参见带演示的SQL小提琴.
这也可以使用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.
unpivot进程会将您的多列转换为多行数据.您没有指定要使用的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小提琴.这会将您的数据转换为以下格式:
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;
请参见带有演示的SQL小提琴
两者都给出以下结果:
| FIRST1 | LAST1 | FIRST2 | LAST2 | FIRST3 | LAST3 | FIRST4 | LAST4 | FIRST5 | LAST5 |
|-------------|-------------|-------------|-------------|-------------|-------------|-------------|-------------|-------------|-------------|
| RandomName1 | RandomLast1 | RandomName2 | RandomLast2 | RandomName3 | RandomLast3 | RandomName4 | RandomLast4 | RandomName5 | RandomLast5 |
这篇关于SQL Server-PIVOT-两列成行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
