SQL 动态SQL生成列名？

Dynamic SQL to generate column names?

提问by Ben McCormack

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...) As '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 OrderDetailDeliveryReviewquery and then execute the resulting SQL code?

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

回答by Cade Roux

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)

回答by van

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 Column Data 中得到解答。
另请参阅在 SQL 中创建交叉表查询和数据透视表。

回答by Mitch Stokely

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)

回答by madhusudan

-- 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)