SQL Pivot Example

Using the PIVOT statement
If you need an ad-hoc mechanism to create a falt file data table using pure SQL - look no further than the PIVOT statement. PIVOT may be used to produce columns of data which correlate to months or time periods, over rows that correlate to accounts, regions, etc. In the query below, assume you have a table Demo_Company with a CompanyID column, which joins to CompanyID in Demo_CompanySales table which has Sale Dates & Amounts.
The syntax is a bit tricky - but using PIVOT can give you the results you need in a flat file format with relatively little effort. Add an INTO statement before the FROM and you can retain the table for future use or analytical queries.


            

SELECT C.CompanyID AS [CompanyID], 
C.CompanyName AS [CompanyName],
COALESCE([2019_12_Sales],0) AS [2019_12_Sales], 
COALESCE([2019_11_Sales],0) AS [2019_11_Sales], 
COALESCE([2019_10_Sales],0) AS [2019_10_Sales], 
COALESCE([2019_9_Sales],0) AS [2019_9_Sales], 
COALESCE([2019_8_Sales],0) AS [2019_8_Sales], 
COALESCE([2019_7_Sales],0) AS [2019_7_Sales], 
COALESCE([2019_6_Sales],0) AS [2019_6_Sales], 
COALESCE([2019_5_Sales],0) AS [2019_5_Sales], 
COALESCE([2019_4_Sales],0) AS [2019_4_Sales], 
COALESCE([2019_3_Sales],0) AS [2019_3_Sales], 
COALESCE([2019_2_Sales],0) AS [2019_2_Sales], 
COALESCE([2019_1_Sales],0) AS [2019_1_Sales]
FROM  
Demo_Company C 
LEFT OUTER JOIN 
(SELECT CompanyID, CONVERT(varchar(4),YEAR(SaleDate)) + '_' +  CONVERT(varchar(2),MONTH(SaleDate)) + '_Sales' AS SalePeriod, amount   
    FROM Demo_CompanySales
	WHERE YEAR(SaleDate)  = 2019
) AS SourceTable  
PIVOT  
(  
SUM(Amount)
FOR SalePeriod IN (
[2019_12_Sales], 
[2019_11_Sales], 
[2019_10_Sales], 
[2019_9_Sales], 
[2019_8_Sales], 
[2019_7_Sales], 
[2019_6_Sales], 
[2019_5_Sales], 
[2019_4_Sales], 
[2019_3_Sales],
[2019_2_Sales], 
[2019_1_Sales]
)  
) AS SalesTable 
ON C.CompanyID = SalesTable.CompanyID 

ORDER BY C.CompanyID

To slightly dissect whats happening above - The first section is setting which columns to select from the company table and joining the company table and the pivoted table (which is a derived table).

SELECT ... FROM Demo_Company C LEFT OUTER JOIN (

We are selecting the sales data in source table:

(SELECT ... FROM Demo_CompanySales WHERE YEAR(SaleDate)  = 2019) As SourceTable

Create a field called SalePeriod like: YYYY_MM_Sales

CONVERT(varchar(4),YEAR(SaleDate)) + '_' +  CONVERT(varchar(2),MONTH(SaleDate)) + '_Sales' AS SalePeriod

SourceTable should create a table internally that looks like this:

CompanyID	SalePeriod	amount
2	2019_5_Sales	513.00
3	2019_1_Sales	283.00
2	2019_9_Sales	620.00
2	2019_9_Sales	59.00
1	2019_1_Sales	11.00
2	2019_7_Sales	588.00
1	2019_4_Sales	649.00
3	2019_6_Sales	711.00
3	2019_9_Sales	132.00

Pivot must look for columns with SalePeriod like "2019_1_Sales", "2019_2_Sales" and SUM them.

PIVOT (SUM(Amount) FOR SalePeriod IN (...) ) AS SalesTable

AND finally the end of the LEFT OUTER JOIN that joins the derived, pivoted table to the company table.

) ON C.CompanyID = SalesTable.CompanyID

In this example - you should end up with a table that looks like this:

ID   CompanyName	2019_12_Sales	2019_11_Sales	2019_10_Sales	2019_9_Sales	2019_8_Sales	2019_7_Sales	2019_6_Sales	2019_5_Sales	2019_4_Sales	2019_3_Sales	2019_2_Sales	2019_1_Sales
1   ABC Bikes	        61350.00	78056.00	79326.00	73712.00	73105.00	64485.00	65385.00	55733.00	66984.00	83545.00	72989.00	73827.00
2   The Bike Pros	68812.00	79788.00	70909.00	72958.00	67381.00	61599.00	59545.00	71164.00	63817.00	66984.00	61544.00	71617.00
3   Wheelmasters	65598.00	68766.00	71998.00	68521.00	84285.00	69996.00	72738.00	71704.00	76020.00	67462.00	63985.00	76509.00

This above analysis table is based on source data which would look like:

TABLE: Demo_Company
CompanyID   CompanyName
1           ABC Bikes
2           The Bike Pros
3           Wheelmasters

TABLE: Demo_CompanySales
ID  CompanyID   SaleDate    Amount
1   1           1/17/2019   500.00
2   2           1/17/2019   210.00
3   1           1/18/2019   350.00
4   3           1/18/2019   21.00
5   2           1/19/2019   980.00
6   2           1/20/2019   175.00
... 
(5000 rows of sales - all of 2019)