我在单个列中看到了很多关于 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 行,包括名字和姓氏。 "第一个"和"最后一个"列的值将是随机的。
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
谁能给我一点帮助? 谢谢。
基于希拉·
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的答案类似,您可以使用带有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 摆弄演示。
这也可以使用 PIVOT 函数编写,但是由于您想要透视多个列,那么您首先需要查看取消透视First
和Last
列。
取消透视过程会将多列转换为多行数据。 您没有指定正在使用的 SQL Server 版本,但您可以使用带有UNION ALL
CROSS APPLY
的SELECT
,甚至可以使用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 摆弄演示。这会将数据转换为以下格式:
| 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 小提琴
两者都给出以下结果:
| FIRST1 | LAST1 | FIRST2 | LAST2 | FIRST3 | LAST3 | FIRST4 | LAST4 | FIRST5 | LAST5 |
|-------------|-------------|-------------|-------------|-------------|-------------|-------------|-------------|-------------|-------------|
| RandomName1 | RandomLast1 | RandomName2 | RandomLast2 | RandomName3 | RandomLast3 | RandomName4 | RandomLast4 | RandomName5 | RandomLast5 |
试试这样的事情
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]