动态SQL生成列名? [英] Dynamic SQL to generate column names?
问题描述
我有一个查询,我尝试将行值转换为列名,当前正在使用 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动态生成
查询,然后执行生成的SQL代码? SUM(Case ...
语句
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
在您的特定情况下(使用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)
这篇关于动态SQL生成列名?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!