动态SQL生成列名？

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:

我有一个查询，我正在尝试将行行值转换为列名称，目前我正在使用SUM（Case ...）作为'ColumnName'语句，如下所示：

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

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:

以上查询效果很好，并且完全符合我的需要。但是，我正在根据以下查询的结果手写出SUM（Case ...语句）：

Select Distinct Sku2 From OrderDetailDeliveryReview

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?

有没有办法，在存储过程中使用T-SQL，我可以动态生成SUM（来自Select Distinct Sku2来自OrderDetailDeliveryReview查询的Case ...语句，然后执行生成的SQL代码？

4 个解决方案

#1

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

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

SQL Dynamic Pivot - how to order columns

SQL Dynamic Pivot - 如何对列进行排序

SQL Server 2005 Pivot on Unknown Number of Columns

SQL Server 2005在未知列数上进行透视

What SQL query or view will show "dynamic columns"

什么SQL查询或视图将显示“动态列”

How do I Pivot on an XML column's attributes in T-SQL

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

How to apply the DRY principle to SQL Statements that Pivot Months

如何将DRY原则应用于数月透视的SQL语句

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

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

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)

#2

I know that SO search engine is not perfect, but your question has been answered in SQL Server PIVOT Column Data.
Also see Creating cross tab queries and pivot tables in SQL.

我知道SO搜索引擎并不完美，但您的问题已在SQL Server PIVOT列数据中得到解答。另请参阅在SQL中创建交叉表查询和数据透视表。

#3

Why do this using hard coded column names when you can pull all this dynamically from any table?

当您可以从任何表中动态提取所有这些时，为什么使用硬编码列名？

Using UNPIVOT and COALESCE, I can dynamically pull a list of columns from any table and associated column values for any record in a record listing and combine them in a list of column names with values by row. Here is the code. Just drop in your database and table name. The column/value table will be generated for you in SQL Server. Keep in mind, to get a shared column of values for the columns you want to convert to sql variant or text strings. But a great way to get a sample column list of values with matching column names and types with our while loops or cursors. Its pretty fast:

使用UNPIVOT和COALESCE，我可以动态地从记录列表中的任何记录中的任何表和关联列值中提取列列表，并将它们组合在列名称列表中，逐行列出值。这是代码。只需输入您的数据库和表名即可。将在SQL Server中为您生成列/值表。请记住，要为要转换为sql变体或文本字符串的列获取共享的值列。但是使用while循环或游标获取具有匹配列名称和类型的值的列列表的好方法。它非常快：

-- First get a list of all known columns in your database, dynamically...
DECLARE @COLUMNS nvarchar(max)
SELECT @COLUMNS = 

CASE
 WHEN A.DATA_TYPE = 'nvarchar' OR A.DATA_TYPE = 'ntext' THEN 
COALESCE(@COLUMNS + ',','') + 'CAST(CONVERT(nvarchar(4000),['+A.[name]+']) AS sql_variant) AS ['+A.[name]+']'
 WHEN A.DATA_TYPE = 'datetime' OR A.DATA_TYPE = 'smalldatetime' THEN 
COALESCE(@COLUMNS + ',','') + 'CAST(CONVERT(nvarchar,['+A.[name]+'],101) AS sql_variant) AS ['+A.[name]+']'
 ELSE
 COALESCE(@COLUMNS + ',','') + 'CAST(['+A.[name]+'] AS sql_variant) AS ['+A.[name]+']'
 END

FROM
(
 SELECT
 A.name,
 C.DATA_TYPE
 FROM YOURDATABASENAME.dbo.syscolumns A
 INNER JOIN YOURDATABASENAME.dbo.sysobjects B ON B.id = A.id
 LEFT JOIN
 (
 SELECT 
COLUMN_NAME,
 DATA_TYPE
 FROM YOURDATABASENAME.INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'YOURTABLENAME' 
) C ON C.COLUMN_NAME = A.name
 WHERE B.name = 'YOURTABLENAME'
 AND C.DATA_TYPE <> 'timestamp' 
) A
-- Test that the formatted columns list is returned...
--SELECT @COLUMNS

-- This gets a second string list of all known columns in your database, dynamically...
DECLARE @COLUMNS2 nvarchar(max)
SELECT @COLUMNS2 = COALESCE(@COLUMNS2 + ',','') + '['+A.[name]+']'
FROM
(
 SELECT
 A.name,
 C.DATA_TYPE
 FROM YOURDATABASENAME.dbo.syscolumns A
 INNER JOIN YOURDATABASENAME.dbo.sysobjects B ON B.id = A.id
 LEFT JOIN
 (
 SELECT 
COLUMN_NAME,
 DATA_TYPE
 FROM YOURDATABASENAME.INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'YOURTABLENAME' 
) C ON C.COLUMN_NAME = A.name
 WHERE B.name = 'YOURTABLENAME'
 AND C.DATA_TYPE <> 'timestamp' 
) A
-- Test that the formatted columns list is returned...
--SELECT @COLUMNS2


-- Now plug in the list of the dynamic columns list into an UNPIVOT to get a Column Name / Column Value list table...
DECLARE @sql nvarchar(max)
SET @sql = 
'
SELECT
ColumnName,ColumnValue
FROM
(
SELECT
'+@COLUMNS+'
FROM YOURDATABASENAME.dbo.YOURTABLENAME  
WHERE CHANGE_ID IN (SELECT ChangeId FROM YOURDATABASENAME.dbo.OperatorProcess WHERE OperatorProcessID = 3)
) AS SourceTable
UNPIVOT
(
ColumnValue FOR ColumnName IN ('+@COLUMNS2+')
) AS PivotTable
'

EXEC (@sql)

#4

-- Darshankar Madhusudan i can do dynamic columnheading table easly...
--thanks  
declare @incr int = 1,
        @col  int,
        @str varchar(max),
        @tblcrt varchar(max),
        @insrt varchar(max),
        set @tblcrt = 'DECLARE @Results table ('        
        set @str = ''      
        set @insrt = ''  
    select @col = max(column_id) From tempdb.sys.all_columns    where object_id = object_id('tempdb.dbo.#aaa')
    while @incr <= @col
        BEGIN
             SELECT @STR = @STR    +case when @incr = 1 then '''' else ',''' end +rtrim(ltrim(NAME))+'''' FROM TEMPDB.SYS.ALL_COLUMNS    WHERE OBJECT_ID = OBJECT_ID('TEMPDB.DBO.#AAA') and column_id = @incr 
             set @tblcrt = @tblcrt + case when @incr = 1 then '' else ',' end + 'Fld'+CAST(@incr as varchar(3)) +' varchar(50)'
             set @insrt = @insrt   + case when @incr = 1 then '' else ',' end + 'Fld'+CAST(@incr as varchar(3))
             SET @INCR = @INCR + 1 
        END 
        set @tblcrt = @tblcrt + ')'
        set @insrt = 'insert into @Results('+@insrt+') values (' + @STR +')'
        set @tblcrt = @tblcrt+ ';' + @insrt + 'select * from @Results '
exec(@tblcrt)

#1