多年来,我们通过从元数据生成动态数据透视 SQL 来回答了许多这些问题,请看一下这些示例:
SQL 动态透视 - 如何对列进行排序 https://stackoverflow.com/questions/1122117/sql-dynamic-pivot-how-to-order-columns
SQL Server 2005 在未知数量的列上进行透视 https://stackoverflow.com/questions/213702/sql-server-2005-pivot-on-unknown-number-of-columns
哪些 SQL 查询或视图将显示“动态列” https://stackoverflow.com/questions/219559/what-sql-query-or-view-will-show-dynamic-columns
如何在 T-SQL 中透视 XML 列的属性 https://stackoverflow.com/questions/1580077/how-do-i-pivot-on-an-xml-columns-attributes-in-t-sql
如何将 DRY 原则应用于透视月份的 SQL 语句 https://stackoverflow.com/questions/463377/how-to-apply-the-dry-principle-to-sql-statements-that-pivot-months
在您的特定情况下(使用 ANSI 数据透视而不是 SQL Server 2005 的数据透视功能):
DECLARE @template AS varchar(max)
SET @template = 'SELECT
SKU1
{COLUMN_LIST}
FROM
OrderDetailDeliveryReview
Group By
OrderShipToID,
DeliveryDate,
SKU1
'
DECLARE @column_list AS varchar(max)
SELECT @column_list = COALESCE(@column_list, ',') + 'SUM(Case When Sku2=' + CONVERT(varchar, Sku2) + ' Then Quantity Else 0 End) As [' + CONVERT(varchar, Sku2) + '],'
FROM OrderDetailDeliveryReview
GROUP BY Sku2
ORDER BY Sku2
Set @column_list = Left(@column_list,Len(@column_list)-1)
SET @template = REPLACE(@template, '{COLUMN_LIST}', @column_list)
EXEC (@template)