Wie kann ich Spalten und Zeilen in SQL effizient transponieren?

SQL-Spalten- und Zeilentransposition: Ein praktischer Leitfaden

SQL erfordert häufig das Transponieren von Daten – das Konvertieren der Tabellenausrichtung von vertikal (Spalten) in horizontal (Zeilen) oder umgekehrt. Obwohl der Befehl PIVOT vorhanden ist, kann er umständlich sein. In diesem Leitfaden werden einfachere Alternativen untersucht.

Methode 1: UNION ALL-, Aggregate- und CASE-Anweisung

Dieser Ansatz verwendet UNION ALL zum Entpivotieren, dann eine Aggregatfunktion (hier SUM) und eine CASE-Anweisung zum erneuten Pivotieren:

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

Methode 2: Statisch UNPIVOT und PIVOT

Die Kenntnis der Anzahl der zu transformierenden Spalten ermöglicht eine statische UNPIVOT und PIVOT Lösung:

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

Methode 3: Dynamischer Pivot für variable Spalten

Beim Umgang mit einer dynamischen Anzahl von Spalten und Farben bietet dynamisches SQL eine Lösung:

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)

Diese Methoden bieten vielseitige Ansätze zur Datentransposition in SQL und passen sich an verschiedene Datenmanipulationsanforderungen an.

Das obige ist der detaillierte Inhalt vonWie kann ich Spalten und Zeilen in SQL effizient transponieren?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!