Effiziente Umwandlung von Zeilen in Spalten in SQL Server

Question

Ich suche nach einem effizienten Weg, Zeilen in Spalten in SQL Server umzuwandeln. Ich habe gehört, dass PIVOT nicht sehr schnell ist, und ich muss mit vielen Datensätzen umgehen.

Das ist mein Beispiel:

Id

Value

ColumnName

1

John

FirstName

2

2.4

Amount

3

ZH1E4A

PostalCode

4

Fork

LastName

5

857685

AccountNumber

Dies ist mein Ergebnis:

FirstName

Amount

PostalCode

LastName

AccountNumber

John

2.4

ZH1E4A

Fork

857685

Wie kann ich das Ergebnis erstellen?

Answer 1

Es gibt mehrere Möglichkeiten, wie Sie Daten aus mehreren Zeilen in Spalten transformieren können.

Verwendung von PIVOT

In SQL Server können Sie die PIVOT-Funktion verwenden, um die Daten von Zeilen in Spalten zu transformieren:

select Vorname, Betrag, Postleitzahl, Nachname, Kontonummer
from
(
  select Wert, Spaltenname
  from yourtable
) d
pivot
(
  max(Wert)
  for Spaltenname in (Vorname, Betrag, Postleitzahl, Nachname, Kontonummer)
) piv;

Siehe Demo.

Pivot mit unbekannter Anzahl von Spaltennamen

Wenn Sie eine unbekannte Anzahl von Spaltennamen haben, die Sie transponieren möchten, können Sie dynamisches SQL verwenden:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(Spaltenname) 
                    from yourtable
                    group by Spaltenname, id
                    order by id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = N'SELECT ' + @cols + N' from 
             (
                select Wert, Spaltenname
                from yourtable
            ) x
            pivot 
            (
                max(Wert)
                for Spaltenname in (' + @cols + N')
            ) p '

exec sp_executesql @query;

Siehe Demo.

Verwendung einer Aggregatfunktion

Wenn Sie die PIVOT-Funktion nicht verwenden möchten, können Sie eine Aggregatfunktion mit einem CASE-Ausdruck verwenden:

select
  max(case when Spaltenname = 'Vorname' then Wert end) Vorname,
  max(case when Spaltenname = 'Betrag' then Wert end) Betrag,
  max(case when Spaltenname = 'Postleitzahl' then Wert end) Postleitzahl,
  max(case when Spaltenname = 'Nachname' then Wert end) Nachname,
  max(case when Spaltenname = 'Kontonummer' then Wert end) Kontonummer
from yourtable

Siehe Demo.

Verwendung mehrerer Joins

Dies könnte auch mit mehreren Joins abgeschlossen werden, aber Sie benötigen eine Spalte, um jede der Zeilen zu verknüpfen, die Sie nicht in Ihren Beispieldaten haben. Aber die grundlegende Syntax wäre:

select vn.Wert as Vorname,
  a.Wert as Betrag,
  plz.Wert as Postleitzahl,
  ln.Wert as Nachname,
  knr.Wert as Kontonummer
from yourtable vn
left join yourtable a
  on vn.somecol = a.somecol
  and a.Spaltenname = 'Betrag'
left join yourtable plz
  on vn.somecol = plz.somecol
  and plz.Spaltenname = 'Postleitzahl'
left join yourtable ln
  on vn.somecol = ln.somecol
  and ln.Spaltenname = 'Nachname'
left join yourtable knr
  on vn.somecol = knr.somecol
  and knr.Spaltenname = 'Kontonummer'
where vn.Spaltenname = 'Vorname'

Answer 2

Ich habe Taryns Antwort ("Pivot mit unbekannter Anzahl von Spaltennamen" Version) modifiziert, um mehr als 1 Zeile im Ergebnis anzuzeigen. Dafür ist eine zusätzliche "Gruppe" erforderlich

DROP TABLE #yourtable
CREATE table #yourtable
    ([Id] int,[Group] int, [Value] varchar(6), [ColumnName] varchar(13))
;

INSERT INTO #yourtable
    ([Id],[Group], [Value], [ColumnName])
VALUES
    (1,1, 'John', 'FirstName'),
    (2,1, '2.4', 'Amount'),
    (3,1, 'ZH1E4A', 'PostalCode'),
    (4,1, 'Fork', 'LastName'),
    (5,1, '857685', 'AccountNumber'),
    (6,2, 'Pedro', 'FirstName'),
    (7,2, '5.1', 'Amount'),
    (8,2, '123456', 'PostalCode'),
    (9,2, 'Torres', 'LastName'),
    (10,2, '857686', 'AccountNumber')
;
;

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(ColumnName) 
                    from #yourtable
                    group by [Group], ColumnName, id
                    having [group] = (SELECT TOP 1 MIN([Group])FROM #yourtable)
                    order by id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = N'SELECT ' + @cols + N' from 
             (
                select value, ColumnName,[Group]
                from #yourtable
                GROUP BY [Group],ColumnName,Value
            ) x
            pivot 
            (
                max(value)
                for ColumnName in (' + @cols + N')
            ) p '

exec sp_executesql @query;

Answer 3

Eine weitere Option, die sehr nützlich sein könnte, ist die Verwendung von CROSS APPLY

-- Ursprüngliche Daten
SELECT * FROM (VALUES ('1', 1, 2, 3),('2', 11, 22, 33)) AS Stage(id,col1,col2,col3)

-- Zeile in Spalten umwandeln mit CROSS APPLY
SELECT Stage.id,v.idd, v.colc
FROM (VALUES ('1', 1, 2, 3),('2', 11, 22, 33)) AS Stage(id,col1,col2,col3)
CROSS APPLY (VALUES ('col1', col1),('col2', col2),('col3', col3)) AS v(idd,colc)
GO

Answer 4

Bitte versuchen Sie es

CREATE TABLE pvt (Present int, [Absent] int);
GO
INSERT INTO pvt VALUES (10,40);
GO
--Die Tabelle unpivotieren.
SELECT Code, Value
FROM 
   (SELECT Present, Absent
   FROM pvt) p
UNPIVOT
   (Value FOR Code IN 
      (Present, [Absent])
)AS unpvt;
GO

DROP TABLE pvt