SQL Server select query dynamic column output

Question

I want to accomplish the following in SQL Server 2008

I have an article table like follows

| ArticleId | Description |
|-----------+-------------|
|         1 | Test        |
|-----------+-------------|

And a order forecast table like this.

| ArticleId | Week | Order | Amount |
|-----------+--------------+--------+
|         1 |   51 |     1 |      0 |
|         1 |   52 |     2 |    150 |
|         1 |    1 |     3 |      0 |
|         1 |    2 |     4 |    200 |
|         1 |    3 |     5 |      0 |
|-----------+------+-------+--------+

Is there a way to create a query the produces a column for each record in the forecast table in the order of the order column. If it's possible how could I do that?

| ArticleId | Description | Week51 | Week52 | Week1 | Week2 | Week3 |
|-----------+-------------+-----------------+-------+-------+-------+
|         1 | Test        |     0  |    150 |     0 |   200 |     0 |
|-----------+-------------+--------+--------+-------+-------+-------+

Without using dynamic sql you are bound to a CASE Statement with X weeks of arguments or a PIVOT with X columns for week numbers, however, I don't know about hiding null columns. — Ross Bush, Jan 16 '19 at 15:34
You need a dynamic SQL pivot that builds the select list on the order specified. https://stackoverflow.com/questions/19015950/sort-columns-for-dynamic-pivot — EzLo, Jan 16 '19 at 15:36
Possible duplicate of [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) and/or [T-SQL dynamic pivot](https://stackoverflow.com/questions/12210692/t-sql-dynamic-pivot)? — Thom A, Jan 16 '19 at 15:36

score 2 · Accepted Answer · answered Jan 16 '19 at 17:42

Provided the WEEK numbers and Order numbers are consistent, it is a small matter to maintain the column sequence.

You may notice I used #forecast and #article because I did not know your actual table names.

Example

Declare @SQL varchar(max) = '
Select *
 From (
        Select A.ArticleID
              ,D.Description 
              ,B.*
         From  #forecast  A
         Join  #article   D on A.ArticleID=D.ArticleID
         Cross Apply (values (''Week''+left(Week,4),Amount) ) B(Item,Value)
      ) A
 Pivot (max([Value]) 
        For [Item] in (' + Stuff((Select ','+QuoteName('Week'+left(Week,4)) 
                                   From (Select Distinct top 100 [Order],Week From #forecast Order by [Order]  ) A  
                                   For XML Path('')),1,1,'') + ') ) p'
Exec(@SQL);
--Print @SQL

Returns

ArticleID   Description Week51  Week52  Week1   Week2   Week3
1           Test        0       150     0       200     0

Joel Coehoorn · Answer 2 · 2019-01-16T15:49:06.710

In the general case, the SQL language strictly requires the number and data types of result columns be known at query compile time. What you're asking for here can't be known until after the execution plan is prepared and you start looking in the data.

Therefore, the best you'll be able to do generally is run this as three separate steps:

Run a query to tell you about the columns you'll want to use.
Use the results from #1 to compose a new query on the fly.
Execute the new query and return the results

Even then, this kind of pivot is typically better handled in your client code or report tool. The only good news is it's still possible to accomplish all this from most platforms with a single long SQL string.

For this specific situation, where you're clearly looking at week numbers, you can work around the issue by assuming all 53 possible weeks up front (not 52, because of partial weeks at the end of the year!), and writing a large SQL statement which manually accounts for all 55 columns (53 weeks + Article and Description).

Yeah I was afraid this would be the answer, the tool I need this for is not really scripting friendly. That's why my question. — Mark Baijens, Jan 16 '19 at 15:47

score 0 · Answer 3 · answered Jan 16 '19 at 17:13

You can try the following query using Pivot for your desired result.

Create Table Article (ArticleId Int, [Description] Varchar(10))
Insert Into Article Values (1, 'Test')

Create Table OrderForecast(ArticleId Int, [Week] Int, [Order] Int, Amount Int)
Insert Into OrderForecast Values (1, 51, 1, 0),(1, 52, 2, 150), (1, 1, 3, 0),(1, 2, 4, 200), (1, 3, 5,0)

Select ArticleId, [Description], Week51, Week52, Week1, Week2, Week3
from
(
  select ArticleId, [Description], Amount, [Week]
  from 
    (
    SELECT OrderForecast.ArticleId, 'Week' + Convert(Varchar(10), OrderForecast.[Week]) as [Week], [Order], Amount, 
        Article.[Description] as [Description] FROM OrderForecast
    Inner Join Article On OrderForecast.ArticleId = Article.ArticleId
    )a
) d
pivot
(
  max(Amount)
  for [Week] in (Week51, Week52, Week1, Week2, Week3)
) piv;

The result will be as shown below

ArticleId   Description Week51  Week52  Week1   Week2   Week3
-------------------------------------------------------------
1           Test        0       150     0       200     0

Here I have used query as table because week was in numbers like 1, 2 but you want the result in the Week1, Week2, etc. So I have concatenated word Week in the number and used it in the Pivot query.

You can find the live demo Live Demo Here

SQL Server select query dynamic column output

3 Answers3