Dynamic SQL to generate column names?
Solution 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)
Solution 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.
Solution 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)