Dynamic SQL to generate column names?

Question

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?

Answer 1

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.

Answer 2

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

http://stackoverflow.com/questions/1122117/sql-dynamic-pivot-how-to-order-columns

http://stackoverflow.com/questions/213702/sql-server-2005-pivot-on-unknown-number-of-columns

http://stackoverflow.com/questions/219559/what-sql-query-or-view-will-show-dynamic-columns

http://stackoverflow.com/questions/1580077/how-do-i-pivot-on-an-xml-columns-attributes-in-t-sql

http://stackoverflow.com/questions/463377/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)