Wie kann ich in SQL einfach Spalten mit Zeilen vertauschen? Gibt es einen einfachen Befehl zum Transponieren?
D.h. dieses Ergebnis umdrehen:
Paul | John | Tim | Eric
Red 1 5 1 3
Green 8 4 3 5
Blue 2 2 9 1
in dies:
Red | Green | Blue
Paul 1 8 2
John 5 4 2
Tim 1 3 9
Eric 3 5 1
PIVOT
scheint für dieses Szenario zu komplex.
Es gibt mehrere Möglichkeiten, wie Sie diese Daten umwandeln können. In Ihrem ursprünglichen Beitrag haben Sie angegeben, dass "PIVOT" für dieses Szenario zu komplex zu sein scheint, aber es kann sehr einfach mit den Funktionen [UNPIVOT] und PIVOT in SQL Server angewendet werden.
Wenn Sie jedoch keinen Zugriff auf diese Funktionen haben, kann dies mit UNION ALL
auf UNPIVOT
und dann einer Aggregatfunktion mit einer CASE
-Anweisung auf PIVOT
repliziert werden:
Tabelle erstellen:
CREATE TABLE yourTable([color] varchar(5), [Paul] int, [John] int, [Tim] int, [Eric] int);
INSERT INTO yourTable
([color], [Paul], [John], [Tim], [Eric])
VALUES
('Red', 1, 5, 1, 3),
('Green', 8, 4, 3, 5),
('Blue', 2, 2, 9, 1);
Union All, Aggregate und CASE Version:
select name,
sum(case when color = 'Red' then value else 0 end) Red,
sum(case when color = 'Green' then value else 0 end) Green,
sum(case when color = 'Blue' then value else 0 end) Blue
from
(
select color, Paul value, 'Paul' name
from yourTable
union all
select color, John value, 'John' name
from yourTable
union all
select color, Tim value, 'Tim' name
from yourTable
union all
select color, Eric value, 'Eric' name
from yourTable
) src
group by name
Siehe SQL Fiddle mit Demo
Die Anweisung UNION ALL
führt den UNPIVOT
der Daten durch, indem die Spalten Paul, John, Tim, Eric
in separate Zeilen umgewandelt werden. Dann wenden Sie die Aggregatfunktion sum()
mit der Anweisung case
an, um die neuen Spalten für jede Farbe
zu erhalten.
Unpivot und Pivot Statische Version:
Die Funktionen "UNPIVOT" und "PIVOT" in SQL Server machen diese Umwandlung viel einfacher. Wenn Sie alle Werte kennen, die Sie umwandeln möchten, können Sie sie in eine statische Version fest einprogrammieren, um das Ergebnis zu erhalten:
select name, [Red], [Green], [Blue]
from
(
select color, name, value
from yourtable
unpivot
(
value for name in (Paul, John, Tim, Eric)
) unpiv
) src
pivot
(
sum(value)
for color in ([Red], [Green], [Blue])
) piv
Siehe SQL Fiddle mit Demo
Die innere Abfrage mit UNPIVOT
führt die gleiche Funktion aus wie UNION ALL
. Sie nimmt die Liste der Spalten und verwandelt sie in Zeilen, der PIVOT
führt dann die endgültige Umwandlung in Spalten durch.
Dynamische Pivot-Version:
Wenn Sie eine unbekannte Anzahl von Spalten (in Ihrem Beispiel "Paul, John, Tim, Eric") und eine unbekannte Anzahl von Farben haben, die umgewandelt werden sollen, können Sie dynamisches Sql verwenden, um die Liste für "UNPIVOT" und dann für "PIVOT" zu erstellen:
DECLARE @colsUnpivot AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@colsPivot as NVARCHAR(MAX)
select @colsUnpivot = stuff((select ','+quotename(C.name)
from sys.columns as C
where C.object_id = object_id('yourtable') and
C.name <> 'color'
for xml path('')), 1, 1, '')
select @colsPivot = STUFF((SELECT ','
+ quotename(color)
from yourtable t
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query
= 'select name, '+@colsPivot+'
from
(
select color, name, value
from yourtable
unpivot
(
value for name in ('+@colsUnpivot+')
) unpiv
) src
pivot
(
sum(value)
for color in ('+@colsPivot+')
) piv'
exec(@query)
Siehe SQL Fiddle mit Demo
Die dynamische Version fragt sowohl yourtable
als auch die Tabelle sys.columns
ab, um die Liste der Elemente für UNPIVOT
und PIVOT
zu erstellen. Diese wird dann zu einem Abfrage-String hinzugefügt, der ausgeführt werden soll. Der Vorteil der dynamischen Version besteht darin, dass bei einer sich ändernden Liste von "Farben" und/oder "Namen" die Liste zur Laufzeit erstellt wird.
Alle drei Abfragen liefern das gleiche Ergebnis:
| NAME | RED | GREEN | BLUE |
-----------------------------
| Eric | 3 | 5 | 1 |
| John | 5 | 4 | 2 |
| Paul | 1 | 8 | 2 |
| Tim | 1 | 3 | 9 |
Dies erfordert normalerweise, dass Sie vorher ALLE Spalten- UND Zeilenbezeichnungen kennen. Wie Sie in der nachstehenden Abfrage sehen können, werden die Bezeichnungen sowohl bei der UNPIVOT- als auch bei der (erneuten) PIVOT-Operation vollständig aufgeführt.
MS SQL Server 2012 Schema einrichten:
create table tbl (
color varchar(10), Paul int, John int, Tim int, Eric int);
insert tbl select
'Red' ,1 ,5 ,1 ,3 union all select
'Green' ,8 ,4 ,3 ,5 union all select
'Blue' ,2 ,2 ,9 ,1;
Abfrage 1:
select *
from tbl
unpivot (value for name in ([Paul],[John],[Tim],[Eric])) up
pivot (max(value) for color in ([Red],[Green],[Blue])) p
| NAME | RED | GREEN | BLUE |
-----------------------------
| Eric | 3 | 5 | 1 |
| John | 5 | 4 | 2 |
| Paul | 1 | 8 | 2 |
| Tim | 1 | 3 | 9 |
Basierend auf dieser Lösung von bluefeet ist hier eine gespeicherte Prozedur, die dynamisches Sql verwendet, um die transponierte Tabelle zu erzeugen. Sie setzt voraus, dass alle Felder numerisch sind, mit Ausnahme der transponierten Spalte (der Spalte, die in der resultierenden Tabelle die Überschrift sein wird):
/****** Object: StoredProcedure [dbo].[SQLTranspose] Script Date: 11/10/2015 7:08:02 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Paco Zarate
-- Create date: 2015-11-10
-- Description: SQLTranspose dynamically changes a table to show rows as headers. It needs that all the values are numeric except for the field using for transposing.
-- Parameters: @TableName - Table to transpose
-- @FieldNameTranspose - Column that will be the new headers
-- Usage: exec SQLTranspose <table>, <FieldToTranspose>
-- =============================================
ALTER PROCEDURE [dbo].[SQLTranspose]
-- Add the parameters for the stored procedure here
@TableName NVarchar(MAX) = '',
@FieldNameTranspose NVarchar(MAX) = ''
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @colsUnpivot AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@queryPivot AS NVARCHAR(MAX),
@colsPivot as NVARCHAR(MAX),
@columnToPivot as NVARCHAR(MAX),
@tableToPivot as NVARCHAR(MAX),
@colsResult as xml
select @tableToPivot = @TableName;
select @columnToPivot = @FieldNameTranspose
select @colsUnpivot = stuff((select ','+quotename(C.name)
from sys.columns as C
where C.object_id = object_id(@tableToPivot) and
C.name <> @columnToPivot
for xml path('')), 1, 1, '')
set @queryPivot = 'SELECT @colsResult = (SELECT '',''
+ quotename('+@columnToPivot+')
from '+@tableToPivot+' t
where '+@columnToPivot+' <> ''''
FOR XML PATH(''''), TYPE)'
exec sp_executesql @queryPivot, N'@colsResult xml out', @colsResult out
select @colsPivot = STUFF(@colsResult.value('.', 'NVARCHAR(MAX)'),1,1,'')
set @query
= 'select name, rowid, '+@colsPivot+'
from
(
select '+@columnToPivot+' , name, value, ROW_NUMBER() over (partition by '+@columnToPivot+' order by '+@columnToPivot+') as rowid
from '+@tableToPivot+'
unpivot
(
value for name in ('+@colsUnpivot+')
) unpiv
) src
pivot
(
sum(value)
for '+@columnToPivot+' in ('+@colsPivot+')
) piv
order by rowid'
exec(@query)
END
Sie können es mit der Tabelle testen, die mit diesem Befehl bereitgestellt wird:
exec SQLTranspose 'yourTable', 'color'