Dynamic SQL to generate column names?

by

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)

More Related Contents:

  1. Getting result of dynamic SQL into a variable for sql-server
  2. Is it possible to write single query on two table which are not connected to each other?
  3. Get top 1 row of each group
  4. SQL update query using joins
  5. How to update two tables in one statement in SQL Server 2005?
  6. Referring to a Column Alias in a WHERE Clause
  7. SQL query to select dates between two dates
  8. Convert Month Number to Month Name Function in SQL
  9. Create a date from day month and year with T-SQL
  10. SET versus SELECT when assigning variables?
  11. Selecting COUNT(*) with DISTINCT
  12. Generate a resultset of incrementing dates in TSQL
  13. How to get difference between two rows for a column field?
  14. Better techniques for trimming leading zeros in SQL Server?
  15. SQL – Subtracting a depleting value from rows
  16. How to print VARCHAR(MAX) using Print Statement?
  17. What’s the best way to select the minimum value from several columns?
  18. Concatenate values based on ID
  19. Splitting delimited values in a SQL column into multiple rows
  20. SELECT FOR UPDATE with SQL Server
  21. varbinary to string on SQL Server
  22. Is it possible to perform multiple updates with a single UPDATE SQL statement?
  23. Incorrect syntax near the keyword ‘with’…previous statement must be terminated with a semicolon
  24. SQL Server 2005 ROW_NUMBER() without ORDER BY
  25. Can an INNER JOIN offer better performance than EXISTS
  26. Cumulative Total in MS Sql server [duplicate]
  27. Use variable with TOP in select statement in SQL Server without making it dynamic [duplicate]
  28. Compare dates in T-SQL, ignoring the time part
  29. Sql Server : How to use an aggregate function like MAX in a WHERE clause
  30. Memory effective way to read BLOB data in C#/SQL 2005

Leave a Comment