CODEWITHSUNDEEP
sqlsql-serverpivot
user12705030
user12705030

Reputation: 71

SQL Pivot with Month and Year

I'm trying to sort some shipment data using a SQL Pivot but i can not figure it out. I've the data sorted in this way (one row with the total items shipped for a family for each month of each year starting from 2015 to ):

TABLE A

Year | Month | ItemFamilyCode | TotalShipped
2018 |   9   |    FA01        |       5
2018 |   9   |    FA04        |       4
2018 |   10  |    FA01        |       2
2018 |   11  |    FA02        |       1
2018 |   12  |    FA03        |       3
2019 |   1   |    FA04        |       7

and so on. I want to achieve the following result:

ItemFamilyCode | 2018-9 | 2018-10 | 2018-11 | 2018-12 | 2019-1 | [..]
FA01           |    5   |    2    |    0    |    0    |    0   |
FA02           |    0   |    0    |    1    |    0    |    0   |
FA03           |    0   |    0    |    0    |    3    |    0   |
FA04           |    4   |    0    |    1    |    0    |    7   |

and so on ... the family code in order and all the values for each month of each year, from the older month/year to now. Is it possible? Thanks to anyone who can help.

Upvotes: 6

Views: 6180

Answers (1)

XAMT
XAMT

Reputation: 1627

If you want to use it as view :

SELECT * FROM   
(
    SELECT 
        Concat([Year],'-', [Month]) as [Date],
        ItemFamilyCode,
        TotalShipped
    FROM Shipping -- Or any Table Name
) t 
PIVOT(
    Sum(TotalShipped) 
    FOR [Date] IN (
        [2018-9], 
        [2018-10], 
        [2018-11], 
        [2018-12], 
        [2019-1], 
        [2019-2] -- You have to type all months until today
        )
) AS pivot_table;

And, dynamic sql if you can use it in stored procedure :

Make a table with the content of date list to generate date list string

DECLARE 
    @columns NVARCHAR(MAX) = '', 
    @sql     NVARCHAR(MAX) = '';

-- select the category names
SELECT 
    @columns+=QUOTENAME(Date) + ','
FROM 
    DateList
ORDER BY 
    DateList;

-- remove the last comma
SET @columns = LEFT(@columns, LEN(@columns) - 1);

-- construct dynamic SQL
SET @sql ='
SELECT * FROM   
(
    SELECT 
        Concat([Year],'-', [Month]) as [Date],
        ItemFamilyCode,
        TotalShipped 
    FROM Shipping -- Or any Table Name
) t 
PIVOT(
    Sum(TotalShipped)  
    FOR [Date] IN ('+ @columns +')
) AS pivot_table;';

-- execute the dynamic SQL
EXECUTE sp_executesql @sql;

Source : sqlservertutorial

Upvotes: 6

Related Questions