SQL Query: Wie kann ich den Ausgang neu anordnen (Transpose?)

Ich habe ein Ergebnis von SQL server wie folgt:

Zone M1 M2 M3 M4 M5 M6 M7 NORTH 6233 17743 2 6233 6717 7369 7369 SOUTH 12440 20017 7 8057 9724 13418 13418 WEST 33736 30532 5 2184 2056 1944 1944 EAST 3944 14584 2 728 953 970 970 CENTRAL 6233 3636 2 6233 6717 6825 6825 

Aber ich wollte es in folgender Weise:

 M NORTH SOUTH WEST EAST CENTRAL M1 6233 12440 33736 3944 6233 M2 17743 20017 30532 14584 3636 M3 2 7 5 2 2 M4 .... 

Wie macht man das?

Oder wie kann ich es in diesem Format bekommen:

 Zone M Value EAST M1 6322 WEST M1 27387 EAST M2 2345 .... 

Was Sie tun müssen, um das Ergebnis zu erhalten, ist ein zweistufiger process, um sowohl die UNPIVOT als auch die PIVOT functionen zu implementieren.

Der erste Schritt ist, UNPIVOT die data, dies nimmt Ihre mehrere Spalten M1 , M2 , etc. und verwandelt sie in zwei Spalten mit dem Wert und der Spaltenname:

 select zone, value, col from data unpivot ( value for col in ([M1], [M2], [M3], [M4], [M5], [M6], [M7]) ) unpiv; 

Siehe SQL Fiddle mit Demo

Sobald Sie den UNPIVOT , können Sie den PIVOT in die Spalte Zone UNPIVOT :

 select * from ( select zone, value, col from data unpivot ( value for col in ([M1], [M2], [M3], [M4], [M5], [M6], [M7]) ) unpiv ) src pivot ( sum(value) for zone in ([North], [South], [West], [East], [Central]) ) piv; 

Siehe SQL Fiddle mit Demo

Jetzt, wenn du keinen Zugriff auf die PIVOT und UNPIVOT function hast, kannst du das gleiche mit einem UNION ALL für den UNPIVOT und eine aggregierte function mit einem CASE UNPIVOT , um den PIVOT zu replizieren:

 select col, sum(case when zone='North' then value end) North, sum(case when zone='South' then value end) South, sum(case when zone='West' then value end) West, sum(case when zone='East' then value end) East, sum(case when zone='Central' then value end) Central from ( select zone, M1 value, 'M1' col from data union all select zone, M2 value, 'M2' col from data union all select zone, M3 value, 'M3' col from data union all select zone, M4 value, 'M4' col from data union all select zone, M5 value, 'M5' col from data union all select zone, M6 value, 'M6' col from data union all select zone, M7 value, 'M7' col from data ) un group by col 

Siehe SQL Fiddle mit Demo

Schließlich, wenn du eine unbekannte Anzahl von Spalten entweder unpivot oder Pivot hatte, dann könntest du eine dynamische Version davon verwenden:

 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('data') and C.name not in ('zone') for xml path('')), 1, 1, '') select @colsPivot = STUFF((SELECT distinct ',' + quotename(Zone) from data FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'select * from ( select zone, value, col from data unpivot ( value for col in ('+ @colsunpivot +') ) u ) unpiv pivot ( max(value) for zone in ('+ @colspivot +') ) p' exec(@query) 

Siehe SQL Fiddle mit Demo

Alle Versionen erzeugen das gleiche Ergebnis.

Ergebnis:

 | COL | CENTRAL | EAST | NORTH | SOUTH | WEST | ------------------------------------------------- | M1 | 6233 | 3944 | 6233 | 12440 | 33736 | | M2 | 3636 | 14584 | 17743 | 20017 | 30532 | | M3 | 2 | 2 | 2 | 7 | 5 | | M4 | 6233 | 728 | 6233 | 8057 | 2184 | | M5 | 6717 | 953 | 6717 | 9724 | 2056 | | M6 | 6825 | 970 | 7369 | 13418 | 1944 | | M7 | 6825 | 970 | 7369 | 13418 | 1944 |