SQL Server - PIVOT - zwei Spalten in Zeilen
Ich habe viele Fragen zu PIVOT für eine einzelne Spalte gesehen, jede Frage ist komplexer als die andere, aber ich konnte nichts Vergleichbares finden, was ich brauche.
Um ehrlich zu sein, weiß ich nicht einmal, ob mir Pivot in dieser Situation helfen wird.
Nehmen wir an, ich habe folgende Daten in meiner Quelltabelle:
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'
Maximal 5 Zeilen mit Vor- und Nachnamen. Der Wert der Spalten First und Last ist zufällig.
RowId First Last
----- ----------- -----------
1 RandomName1 RandomLast1
2 RandomName2 RandomLast2
3 RandomName3 RandomLast3
4 RandomName4 RandomLast4
5 RandomName5 RandomLast5
Ich habe versucht, diese Daten auf so etwas wie Folgendes zu verlagern:
First1 Last1 First2 Last2 First3 Last3 First4 Last4 First5 Last5
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
RandomName1 RandomLast1 RandomName2 RandomLast2 RandomName3 RandomLast3 RandomName4 RandomLast4 RandomName5 RandomLast5
Zum Beispiel: Ich habe kein Problem, wenn die Spalten First5 und Last5 NULL sind, weil es nur 4 Zeilen gibt.
First1 Last1 First2 Last2 First3 Last3 First4 Last4 First5 Last5
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
RandomName1 RandomLast1 RandomName2 RandomLast2 RandomName3 RandomLast3 RandomName4 RandomLast4 NULL NULL
Kann mir jemand etwas helfen? Vielen Dank.
Lösung basierend auf Sheela KR-Antworten:
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