动态SQL生成列名？

本文介绍了动态SQL生成列名？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个查询，我尝试将行值转换为列名，当前正在使用 SUM（Case ...）作为'ColumnName'语句，像这样：

I have a query where I'm trying pivot row values into column names and currently I'm using SUM(Case...) As 'ColumnName' statements, like so:

SELECT SKU1, SUM(Case When Sku2=157 Then Quantity Else 0 End) As '157', SUM(Case When Sku2=158 Then Quantity Else 0 End) As '158', SUM(Case When Sku2=167 Then Quantity Else 0 End) As '167' FROM OrderDetailDeliveryReview Group By OrderShipToID, DeliveryDate, SKU1

上面的查询效果很好，给了我确切的需求。但是，我正在根据以下查询的结果手动编写 SUM（Case ... 语句：

The above query works great and gives me exactly what I need. However, I'm writing out the SUM(Case... statements by hand based on the results of the following query:

Select Distinct Sku2 From OrderDetailDeliveryReview

是否有一个方式，在存储过程中使用T-SQL，可以从从OrderDetailDeliveryReview中选择不同的Sku2动态生成 SUM（Case ... 语句查询，然后执行生成的SQL代码？

Is there a way, using T-SQL inside a stored procedure, that I can dynamically generate the SUM(Case... statements from the Select Distinct Sku2 From OrderDetailDeliveryReview query and then execute the resulting SQL code?

推荐答案

这些年来已经回答了很多问题通过从元数据生成动态数据透视SQL，请查看以下示例：

Having answered a lot of these over the years by generating dynamic pivot SQL from the metadata, have a look at these examples:

SQL Dynamic Pivot - how to order columns

SQL Server 2005枢轴上的列数未知

哪些SQL查询或视图将显示动态列 ;

如何在T-SQL中透视XML列的属性

如何将DRY原理应用于枢纽月份的SQL语句

在您的特定情况下（使用ANSI数据透视表而不是SQL Server 2005的PIVOT功能）：

In your particular case (using the ANSI pivot instead of SQL Server 2005's PIVOT feature):

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)