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:
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?
