Dynamic SQL to generate column names?

问题:

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 

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 

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?

回答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 Dynamic Pivot - how to order columns

SQL Server 2005 Pivot on Unknown Number of Columns

What SQL query or view will show "dynamic columns"

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

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

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)

回答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.

回答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:

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