How to pivot data for every month dynamically?

Question

I'm checking number of orders of by-franchise-operated or self-operated shops for a company. They are put into a date difference category. Here is the query and result as an explanation:

SELECT [Order-Delivery Difference], [Type], COUNT(DISTINCT OrderId) AS [Number of Orders]
FROM (SELECT DDIFF AS [Order-Delivery Difference], Franchise AS [Type], OrderId, [Net Value]
        FROM joined
        GROUP BY DDIFF, FRANCHISE, OrderId, [Net Value]
      ) AS subquery
GROUP BY [Order-Delivery Difference], [Type]
HAVING SUM([Net Value]) > 0
ORDER BY [Order-Delivery Difference]
;

I have a Date type column in my table, let's call it DateColumn. I can theoretically filter this result by any given specific time.

...
(SELECT DDIFF AS [Order-Delivery Difference], Franchise AS [Type], OrderId, [Net value]
    FROM joined
    WHERE DATEPART(year, [DateColumn]) = 2017 AND DATEPART(month, [DateColumn]) = 1
    GROUP BY DDIFF, FRANCHISE, OrderId, [Net Value]
) AS subquery
...

But I want to pivot this result for every distinct month date available in the Date column, like this:

My questions are: Can I create a pivot table like this, having another column at the beggining? Or should I create a pivot table for every distinct O-D Difference category instead? Is there a practical way to do that?

Also is there a procedural solution to create a column with all the distinct month dates to give that as a dynamic attribute set for the pivot function?

Is there a date column in JOINED what can be used to build the X-Axis 2017.01, 2017.02, ... ? What is the name of that column? — John Cappelletti
@JohnCappelletti Yes, I've mentioned it this time, learning from past mistakes :) JOINED table has rows for every order every made, and there is a date column, registering the actual date the order was created. I edit the post to name this column. — MattSom
I still don't see the NAME of the column. Is it a Date or DateTime? — John Cappelletti

John Cappelletti John Cappelletti · Accepted Answer · 2019-06-27T15:45:35

Perhaps this will help

Example

Declare @SQL varchar(max) = '
Select *
From (
        Select [Order-Delivery Difference] = DDIFF
             , [Type]  = Franchise 
             , [Value] = COUNT(DISTINCT OrderId)
             , [Item]  = left(DateColumn,7)
        From JOINED
        Group By DDIFF,Franchise,left(DateColumn,7)
     ) A
 Pivot (sum(Value) For [Item] in (' + Stuff((Select Distinct ',' + QuoteName(left(DateColumn,7)) From  JOINED Order By 1 For XML Path('')),1,1,'')  + ') ) p
 Order By [Order-Delivery Difference]
         ,Type
'

--Print @SQL
Exec(@SQL);

The Sample Data Looks Like This

The Results Looks Like This

EDIT- CORRECTED FOR FRANCHISE COLUMN

For the STUFF Portion

Select Distinct ',' + QuoteName(left(DateColumn,7)) From  #JOINED

Generates

(No column name)
,[2017-01]
,[2017-02]

When we add the For XML, we get an XML string

,[2017-01],[2017-02]

Then The STUFF(...,1,1,'') removes the leading comma, which becomes the PIVOT Columns

[2017-01],[2017-02]

How to pivot data for every month dynamically?

1 Answers