Sql-Server
在 sql server 中使用匯總數據透視
是否可以使用游標創建動態表，然後使用這些列來聚合 SQL Server 2008 中的數據？
以下表為例。
CREATE TABLE Billing ( BillingId BIGINT IDENTITY, SubscriptionId BIGINT, ExternalServiceName VARCHAR(50), BillYear INT NOT NULL, BillMonth INT NOT NULL ); INSERT INTO Billing (BillingId, SubscriptionId, ExternalServiceName, BillYear, BillMonth) VALUES (1, 1, 'Dogs', 2018, 4), (2, 2, 'Cats', 2018, 4), (3, 1, 'Dogs', 2018, 5), (4, 2, 'Cats', 2018, 5); CREATE TABLE BillingData ( BillingDataId INT IDENTITY PRIMARY KEY, BillingId INT NOT NULL, Feature VARCHAR(50) NOT NULL, Usage INT NOT NULL, Measurement VARCHAR(50), Cost NUMERIC(18,2) NOT NULL ); INSERT INTO BillingData(BillingId, Feature, Usage, Measurement, Cost) VALUES (1, 'Walks', 25, 'walks', 200.32), (1, 'Baths', 5, 'baths', 251.32), (2, 'Litter change', 53, 'changes', 110.21), (2, 'Groom', 25, 'brushings', 123), (2, 'Scratching', 213, 'clipping', 123), (3, 'Pilling', 11, 'medicate', 10), (4, 'Groom', 5, 'brushings', 50), (4, 'Exercise', 1, 'run', 25.12), (1, 'Walks', 500, 'walks', 12351.31), (1, 'Baths', 53, 'baths', 1235), (2, 'Baths', 53, 'baths', 1235);我想做的是用這種格式創建一個表
+-------------+---------+---------+-----------------+---------+--------------+---------+----------+ | [BillingId] | [Walks] | [Baths] | [Litter change] | [Groom] | [Scratching] | [Usage] | [Cost] | +-------------+---------+---------+-----------------+---------+--------------+---------+----------+ | 1 | 525 | 58 | 0 | 0 | 0 | 583 | 14037.95 | | 2 | 0 | 53 | 53 | 25 | 213 | 344 | 1591.21 | +-------------+---------+---------+-----------------+---------+--------------+---------+----------+我能想到的唯一方法是聚合垂直表。
通過執行以下查詢
SELECT MAX(BillingId), MAX(Feature), SUM(Usage), MAX(Measurement), SUM(Cost) FROM BillingData;但是我必須將這些列動態地加入到帳單表中，特別是因為帳單數據可能每個月都不一樣。例如：
SELECT DISTINCT Feature FROM BillingData WHERE BillYear=2018 AND BillMonth=5;不同於
SELECT DISTINCT Feature FROM BillingData WHERE BillYear=2018 and BillMonth=4;因此，雖然 BillingId、Walks、Baths、Litter change、Groom、Scratching、Usage、Cost 列適用於 4 月，但 May 的列將只是 BillingId、Pilling、Groom、Exercise、Usage 和 Cost。
我相信數據透視表可能是我在這裡需要的，但我懷疑它可能需要是動態的，因為每個月的列都需要不同。
我不確定執行此操作的最佳方法。一些幫助將不勝感激。
這可以通過
PIVOT並且可以動態完成，但是在您嘗試動態執行此操作之前，您應該嘗試使用查詢的靜態或硬編碼版本獲得所需的結果，然後將其轉換為動態 sql .由於您使用的是 SQL Server 2008，並且您希望為
Usage和提供一個總列Cost，因此我將首先查看sum(<your column) over(...). 這將允許您在對數據進行透視之前在一個步驟中聚合您的數據。為了獲得靜態版本，我首先從類似於以下的查詢開始：
select b.BillingId, bd.Feature, bd.Usage, TotalUsage = sum(bd.Usage) over(partition by bd.BillingId), TotalCost = sum(bd.Cost) over(partition by bd.BillingId) from Billing b inner join BillingData bd on b.BillingId = bd.BillingId where b.BillYear = 2018 and b.BillMonth = 4請參閱 SQL 小提琴。此查詢為您提供要轉換的基本數據：
| BillingId | Feature | Usage | TotalUsage | TotalCost | |-----------|---------------|-------|------------|-----------| | 1 | Walks | 25 | 583 | 14037.95 | | 1 | Baths | 5 | 583 | 14037.95 | | 1 | Walks | 500 | 583 | 14037.95 | | 1 | Baths | 53 | 583 | 14037.95 | | 2 | Baths | 53 | 344 | 1591.21 | | 2 | Litter change | 53 | 344 | 1591.21 | | 2 | Groom | 25 | 344 | 1591.21 | | 2 | Scratching | 213 | 344 | 1591.21 |包括你
BillingId的，Features你最終想要在一個新列中的每一個，然後是Usage,TotalUsage和TotalCostfor eachBillingId。為您提供每個帳戶的sum(<yourcolumn> over(partition by bd.BillingId)價值，而無需使用GROUP BY. 獲得此數據後，您可以應用該PIVOT功能：select BillingId, Walks = IsNull(Walks, 0), Baths = IsNull(Baths, 0), [Litter Change] = IsNull([Litter Change], 0), Groom = IsNull(Groom, 0), Scratching = IsNull(Scratching, 0), Usage = TotalUsage, Cost = TotalCost from ( select b.BillingId, bd.Feature, bd.Usage, TotalUsage = sum(bd.Usage) over(partition by bd.BillingId), TotalCost = sum(bd.Cost) over(partition by bd.BillingId) from Billing b inner join BillingData bd on b.BillingId = bd.BillingId where b.BillYear = 2018 and b.BillMonth = 4 ) x pivot ( sum(Usage) for Feature in ([Walks], [Baths], [Litter Change], [Groom], [Scratching]) ) piv;請參閱 SQL Fiddle 進行展示。這給出了一個結果：
| BillingId | Walks | Baths | Litter Change | Groom | Scratching | Usage | Cost | |-----------|-------|-------|---------------|-------|------------|-------|----------| | 1 | 525 | 58 | 0 | 0 | 0 | 583 | 14037.95 | | 2 | 0 | 53 | 53 | 25 | 213 | 344 | 1591.21 |現在您已經獲得了您正在尋找的最終結果，您可以開始將查詢轉換為動態 SQL。為了執行此操作，您將需要獲取要成為列的值的列表，即
Feature值。這是通過使用所需的BillYearand查詢表BillMonth，並將值連接成一個字元串，然後獲取該列列表並執行完整的 sql 字元串來完成的。完整的程式碼可能類似於：DECLARE @BillYear int = 2018, @BillMonth int = 4, @colsNull AS NVARCHAR(MAX), @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @cols = STUFF((SELECT ', ' + QUOTENAME(bd.Feature) from Billing b inner join BillingData bd on b.BillingId = bd.BillingId where b.BillYear = @BillYear and b.BillMonth = @BillMonth group by bd.Feature FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') select @colsNull = STUFF((SELECT ', IsNull(' + QUOTENAME(bd.Feature)+',0) as '+ QUOTENAME(bd.Feature) from Billing b inner join BillingData bd on b.BillingId = bd.BillingId where b.BillYear = @BillYear and b.BillMonth = @BillMonth group by bd.Feature FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,''); set @query = N'SELECT BillingId, ' + @colsNUll + N', TotalUsage, TotalCost from ( select b.BillingId, bd.Feature, bd.Usage, TotalUsage = sum(bd.Usage) over(partition by bd.BillingId), TotalCost = sum(bd.Cost) over(partition by bd.BillingId) from Billing b inner join BillingData bd on b.BillingId = bd.BillingId where b.BillYear = '+cast(@BillYear as nvarchar(4))+N' and b.BillMonth = '+cast(@BillMonth as nvarchar(2))+N' ) x pivot ( sum(Usage) for Feature in (' + @cols + N') ) p ' exec sp_executesql @query;請參閱 SQL Fiddle with Demo。您會注意到列有兩個變數 - 一個
@cols在函式內部使用PIVOT，然後@colsNull這與第一個類似，但它將nulls最終選擇列表中的 替換為零 - 如果不這樣做，您可以排除使用它不需要它。如果您執行此操作，BillingMonth = 4您將獲得與靜態版本相同的結果：| BillingId | Baths | Groom | Litter change | Scratching | Walks | TotalUsage | TotalCost | |-----------|-------|-------|---------------|------------|-------|------------|-----------| | 1 | 58 | 0 | 0 | 0 | 525 | 583 | 14037.95 | | 2 | 53 | 25 | 53 | 213 | 0 | 344 | 1591.21 |然後，如果您更改，
BillingMonth = 5您無需更改查詢（Demo）即可獲得結果：| BillingId | Exercise | Groom | Pilling | TotalUsage | TotalCost | |-----------|----------|-------|---------|------------|-----------| | 3 | 0 | 0 | 11 | 11 | 10 | | 4 | 1 | 5 | 0 | 6 | 75.12 |