Mit pivot-Tabelle mit Spalten-und Zeilensummen in sql server 2008
Ich habe eine Tabelle mit folgenden Spalten
defect_id, developer_name, status, summary, root_cause,
Secondary_RC, description, Comments, environment_name
Die Spalte root_cause
hat Enviro, Anforderung en, Dev, TSc, TD, Unkn, wie seine Werte und
Spalte environment_name hat QA1, QA2, QA3
Muss ich einen Bericht erstellen, in dem unter format
Enviro Requi Dev TSc TD Unkn Total
QA1 9 1 14 17 2 3 46
QA2 8 1 14 0 5 1 29
QA3 1 1 7 0 0 1 10
Total 18 3 35 17 7 5 85
Habe ich den Bericht vorzubereiten, bis
Enviro Requi Dev TSc TD Unkn
QA1 9 1 14 17 2 3
QA2 8 1 14 0 5 1
QA3 1 1 7 0 0 1
Ich verwendet die folgenden Abfrage zu bekommen, das obige Ergebnis
select *
from
(
select environment_name as " ", value
from test1
unpivot
(
value
for col in (root_cause)
) unp
) src
pivot
(
count(value)
for value in ([Enviro] , [Requi] , [Dev] , [Tsc], [TD] , [Unkn])
) piv
Kann jemand helfen, um die Gesamtsummen für Zeilen und Spalten?
- Herzlich willkommen auf StackOverflow: wenn Sie die post-code -, XML-oder Daten-samples, bitte markieren Sie die gewünschten Zeilen im text-editor und klicken Sie auf den "code samples" - Taste (
{ }
) auf der editor-Symbolleiste zu schön format und syntax-markieren Sie es! - Was ist der Zweck der
unpivot
Teil in dieser Abfrage? Sicher, Sie könnte ersetzen die gesamte Unterabfrage mit nurselect environment_name as " ", root_cause as value from test1
keine? - Sorry, ich meinte Sie umschreiben könnte es ähnlich.
- möglich, Duplikat der Können Sie Zwischensummen-Zeilen und/oder Spalten in einer pivot-Tabelle?
Du musst angemeldet sein, um einen Kommentar abzugeben.
Möglicherweise gibt es verschiedene Lösungsansätze. Berechnen Sie alle Summen nach den Drehpunkt, oder Sie können die Summen zuerst, dann pivot-alle Ergebnisse. Es ist auch möglich, um eine Art Mittelweg: Holen Sie sich eine Art von Summen (z.B. die Zeilen-Weise), pivot -, dann bekommen Sie die andere Art, obwohl, dass könnte es übertreiben.
Den ersten der genannten Ansätze, immer alle Summen nach den Drehpunkt, getan werden könnte, in eine sehr einfache Weise, und die einzige Sache, die möglicherweise neu für Sie sind unten in der Umsetzung sein könnte
GROUP BY ROLLUP()
:Grundsätzlich die
GROUP BY ROLLUP()
Teil produziert, die Insgesamt Zeile für Sie. Die Gruppierung ist in der ersten gemacht, indemenvironment_name
, dann ist das grand total Zeile Hinzugefügt wird.Tun genau das Gegenteil, D. H. die Summen vor, um zu schwenken, das Sie einsetzen konnte
GROUP BY CUBE()
wie diese:Beide Methoden getestet werden können und spielte auf SQL Fiddle:
Methode 1
Methode 2
Hinweis. Ich habe die weggelassen unpivoting Schritt in beide Vorschläge, weil unpivoting eine einzelne Spalte schien eindeutig redundant. Wenn es mehr zu ihm, obwohl, die Anpassung der beiden Abfragen sollten einfach sein.
FROM test1 PIVOT ...
, dann PIVOT würde alle Spalten verwendet, die intest1
(abgesehen von den angegebenen, wie die Aggregat-Funktion argument) als Gruppierung der Kriterien, aber wir müssen nur eine Gruppe vonenvironment_name
.environment_name
. AberGROUP BY ROLLUP(environment_name)
letztlich Sie unterdrückt und aggregiert die zählt, produziert die gleiche Ausgabe wie mit der Unterabfrage.Finden Sie Insgesamt für
root_cause
undenvironment_name
mitROLLUP
.RNO_COLTOTAL
- Logik zu platzierenTotal
in der letzten Spalte, da die SpaltenTsc
,Unkn
überlappen die SpalteTotal
beim schwenken, da seine Bestellung in alphabetischer Reihenfolge.RNO_ROWTOTAL
- Logik zu platzierenTotal
in der letzten Reihe, da Sie einen Wert, beginnend mitU
,W
,X
,Y
,Z
können sich überlappen, wird der WertTotal
seit seiner Bestellung in alphabetischer Reihenfolge.SUM(VALUE)
- Kann definiert werden, auf welche Aggregat-Funktion, die wir verwenden können, mitROLLUP
.ABFRAGE 1
Wir erhalten die folgende Logik, wenn
CUBE
verwendet wirdWir deklarieren Sie Variablen für Schwenk.
@cols
- Spalte Werte für Schwenk.@NulltoZeroCols
- Ersetzt null-Werte mit null.ABFRAGE 2
Nun pivot dynamisch
ERGEBNIS
Ich glaube, Sie brauchen zum berechnen der Summe getrennt. Mit dieser einfachen Abfrage für die Summe (sorry, musste zu geben, den alias-Namen für Ihre "Spalte"):
können Sie ganz einfach beitreten beide Abfragen zusammen, um den gewünschten Bericht:
Habe ich es nicht getestet, aber glaube, es wird funktionieren