sql - 动态枢轴sql

Question

我正在尝试从客户表、契约(Contract)支出表和契约(Contract)名称表中选择数据。我的 table 看起来像这样

Customers
CustomerID  CustomerName  Address          etc
1           "ABC Corp"   "123 Here Ave"
2           "Acme Corp"  "101 Lets Ave"

ContractTypes
ContractTypeID ContractName
1              "Website Hosting"
2              "Domain Hosting"
3              "Email Hosting"

ServiceSpend
ServiceSpendID  ContractTypeID  CustomerID  Spend
1               2               1           5.99
2               1               1           5.99
3               1               2           9.99

我想制作下表

CustomerID CustomerName Address         DomainHosting WebsiteHosting
1          "ABC Corp"   "123 Here Ave"  9.99          5.99

目前我有以下有效的 sql 语句，但我需要能够动态定义列，因为我们需要能够向数据库添加额外的契约(Contract)名称并仍然报告客户支出

select *
from 
(
SELECT        
Customers.CustomerID, Customers.ContactName, Customers.Address,                              ContractTypes.ContractName AS ContractName, ServiceSpend.Spend
FROM            
Customers INNER JOIN
ServiceSpend ON Customers.CustomerID = ServiceSpend.CustomerID 
INNER JOIN
ContractTypes ON ServiceSpend.ContractTypeID = ContractTypes.ContractTypeID
) src
pivot
(
sum(spend)
    for ContractName in ([Website Hosting],[Domain Hosting])) piv;

有谁知道如何动态添加我的列

Answer 1

您必须使用动态 sql 为您的输出构造列:

DECLARE @Columns VARCHAR(1000) = STUFF((
    SELECT ',[' + ContactName + ']'
    FROM ContactTypes
    FOR XML PATH('')
    ),1,1,'')
DECLARE @Sql VARCHAR(1000) = '
    SELECT *
    FROM (
        SELECT        
            Customers.CustomerID,
            Customers.ContactName,
            Customers.Address,
            ContractTypes.ContractName,
            ServiceSpend.Spend
        FROM Customers
            INNER JOIN ServiceSpend
                ON Customers.CustomerID = ServiceSpend.CustomerID 
            INNER JOIN ContractTypes
                ON ServiceSpend.ContractTypeID = ContractTypes.ContractTypeID
        ) SRC
        PIVOT (SUM(Spend) FOR ContractName IN (' + @Columns + ')) piv;'
EXEC (@Sql)