Динамический SQL для генерации имен столбцов?

У меня есть запрос, где я пытаюсь сводить значения строк в имена столбцов, и в настоящее время я использую 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

Приведенный выше запрос прекрасно работает и дает мне именно то, что мне нужно. Тем не менее, я пишу SUM(Case... Заявления от руки на основании результатов следующего запроса:

Select Distinct Sku2 From OrderDetailDeliveryReview

Есть ли способ, используя T-SQL внутри хранимой процедуры, что я могу динамически генерировать SUM(Case... заявления от Select Distinct Sku2 From OrderDetailDeliveryReview запрос, а затем выполнить полученный код SQL?

Источник

4 ответа

Решение

Ответив на многие из них за эти годы, генерируя динамический сводный SQL из метаданных, взгляните на следующие примеры:

SQL Dynamic Pivot - как упорядочить столбцы

Сводная таблица SQL Server 2005 для неизвестного числа столбцов

Какой запрос или представление SQL будет показывать "динамические столбцы"

Как развернуть атрибуты столбца XML в T-SQL

Как применить принцип СУХОЙ к операторам SQL, которые сводят месяцы

В вашем конкретном случае (используя сводку ANSI вместо функции PIVOT в SQL Server 2005):

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)
Источник

Я знаю, что поисковая система SO не идеальна, но на ваш вопрос дан ответ в столбце SQL Server PIVOT Data.
Также см. Создание запросов кросс-таблицы и сводных таблиц в SQL.

Источник

Зачем делать это с использованием жестко закодированных имен столбцов, когда вы можете извлекать все это динамически из любой таблицы?

Используя 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)
Источник 
-- 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)
Источник
Другие вопросы по тегам