Ich möchte eine Pivot-Tabelle mit 2 Summenaggregaten pro Pivot-Spalte erstellen. Dann möchte ich rechts neben den Pivot-Spalten einige Gesamtspalten haben. Schließlich ist die Anzahl der Pivot-Spalten dynamisch. Mein bevorzugtes Ergebnis würde so aussehen:
Meine Daten sehen folgendermaßen aus:
Mit dieser Antwort bin ich der Lösung ziemlich nahe gekommen. Folgendes habe ich:
SELECT *
FROM (
SELECT B.SiteID, R.BuildingID, C.*
FROM Rooms R JOIN Buildings B
ON R.BuildingID = B.BuildingID
CROSS APPLY (
VALUES(RTRIM(RoomType) + ' NASF', AreaNASF)
,(RTRIM(RoomType) + ' RSF', AreaRSF)
) C (Item,Value)
) src
PIVOT (
SUM([Value])
FOR [Item] IN ([CONFERENCE NASF], [CONFERENCE RSF], [OFFICE NASF], [OFFICE RSF], [STORAGE NASF], [STORAGE RSF])
) pvt
Welches produziert:
Ich habe den Eindruck, dass ich den zweizeiligen Header außerhalb von SQL ausführen muss. Ich brauche Hilfe beim Hinzufügen der Gesamtspalten. Gibt es auch eine bessere Lösung für dynamische Spalten als die STUFF
Lösung, die ich an vielen Stellen gesehen habe?
Hier ist die SQL zum Erstellen der Beispieldaten:
CREATE TABLE Buildings (
BuildingID CHAR(12),
SiteID CHAR(12),
Name VARCHAR(100),
CONSTRAINT PK_Building PRIMARY KEY (BuildingID)
);
CREATE TABLE Rooms (
BuildingID CHAR(12),
FloorID CHAR(4),
RoomID CHAR(8),
RoomType CHAR(16),
Dept CHAR(16),
AreaNASF NUMERIC(12,2),
AreaRSF NUMERIC(12,2),
CONSTRAINT FK_Rooms_BuildingID FOREIGN KEY (BuildingID) REFERENCES Buildings(BuildingID),
CONSTRAINT PK_Rooms PRIMARY KEY (BuildingID, FloorID, RoomID)
);
INSERT INTO Buildings (BuildingID, SiteID, Name) VALUES
('100', 'Main', 'Headquarters'),
('200', 'Main', 'Technology'),
('300', 'Fleet', 'Fleet')
INSERT INTO Rooms (BuildingID, FloorID, RoomID, RoomType, Dept, AreaNASF, AreaRSF) VALUES
('100', '01', '101', 'CONFERENCE', 'FINANCE', 206.84, 207.00)
,('100', '01', '102', 'OFFICE', 'FINANCE', 100.55, 101.00)
,('100', '01', '103', 'OFFICE', 'FINANCE', 100.87, 101.00)
,('100', '02', '201', 'STORAGE', 'FINANCE', 56.15, 0.00)
,('100', '02', '202', 'CONFERENCE', 'FINANCE', 164.93, 160.00)
,('200', '01', '101', 'OFFICE', 'IT', 95.50, 96.00)
,('200', '01', '102', 'OFFICE', 'IT', 100.64, 100.00)
,('200', '01', '103', 'CONFERENCE', 'IT', 220.19, 220.00)
,('200', '01', '104', 'STORAGE', 'IT', 50.25, 0.00)
,('200', '02', '201', 'OFFICE', 'HR', 65.82, 66.00)
,('300', '01', '101', 'OFFICE', 'MAINTENANCE', 65.82, 66.00)
,('300', '01', '102', 'OFFICE', 'MAINTENANCE', 65.82, 66.00)
Hier ist eine Möglichkeit, die sum()Over()
Fensteraggregatfunktion zu verwenden. Trick hier ist das Voraggregieren der AreaNASF/AreaRSF
Säule für jede BuildingID
vor dem Schwenken
SELECT *
FROM (SELECT B.SiteID,
R.BuildingID,
TotalAreaNASF,
TotalAreaRSF,
C.*
FROM (SELECT TotalAreaNASF = Sum(AreaNASF)OVER(partition BY BuildingID),
TotalAreaRSF= Sum(AreaRSF)OVER(partition BY BuildingID),*
FROM Rooms) R
JOIN Buildings B
ON R.BuildingID = B.BuildingID
CROSS APPLY ( VALUES(Rtrim(RoomType) + ' NASF',AreaNASF),
(Rtrim(RoomType) + ' RSF',AreaRSF) ) C (Item, Value)) src
PIVOT ( Sum([Value])
FOR [Item] IN ([CONFERENCE NASF],
[CONFERENCE RSF],
[OFFICE NASF],
[OFFICE RSF],
[STORAGE NASF],
[STORAGE RSF]) ) pvt
Wenn die Anzahl RoomTypes
unbekannt ist, ist hier die dynamische Version
declare @col_list varchar(max),
@sql varchar(max)
set @col_list = stuff((select distinct ','+QUOTENAME(Rtrim(RoomType) + ' NASF')+','+QUOTENAME(Rtrim(RoomType) + ' RSF') from #Rooms for xml path('')),1,1,'')
set @sql = '
SELECT SiteID, BuildingID, '+@col_list+', [Total NASF], [Total RSF]
FROM (SELECT B.SiteID,
R.BuildingID,
[Total NASF],
[Total RSF],
C.*
FROM (SELECT [Total NASF] = Sum(AreaNASF)OVER(partition BY BuildingID),
[Total RSF] = Sum(AreaRSF)OVER(partition BY BuildingID),*
FROM Rooms) R
JOIN Buildings B
ON R.BuildingID = B.BuildingID
CROSS APPLY ( VALUES(Rtrim(RoomType) + '' NASF'',AreaNASF),
(Rtrim(RoomType) + '' RSF'',AreaRSF) ) C (Item, Value)) src
PIVOT ( Sum([Value])
FOR [Item] IN ('+@col_list+') ) pvt '
print @sql
exec (@sql)
Dieser Artikel stammt aus dem Internet. Bitte geben Sie beim Nachdruck die Quelle an.
Bei Verstößen wenden Sie sich bitte [email protected] Löschen.
Lass mich ein paar Worte sagen