An Efficient Way to Turn Rows into Columns to Check the Revenue of Products under Each Month

Question

I have a table with 3 columns which shows product's revenue in each month:

1.Product

2.Revenue$

3.Month_Date

The Table is like:

Product | Month_Date | Revenue$
A          2013-01       10
A          2013-02       11 
A          2013-03       8
...         ...           ...
A          2019-04       20
B          2013-01       2
B          2013-02       5

The problem is that there are 76 months from 2013-01 to 2019-04, and I want to show the month in columns instead of rows and show the revenue for each product in a row so that the data should be like the following table :

          Product | 2013-01 | 2013-02 | 2013-03 | ...... | 2019-04
Revenue$   A           10        11       8       ......    20  
           B            2         5       3       ......     6

Could you give me a hand by telling me if there is any efficient way to do this please? I have tried a long time without working this out instead of hard coding.Thank you so much for your help in advance!

Possible duplicate of [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — Mark Rotteveel, May 13 '19 at 09:33

score 0 · Answer 1 · answered May 13 '19 at 07:55

For Sql Server you can see this example.

Extract from page:

SELECT *
FROM
(
    SELECT [PolNumber],
           [PolType],
           [Effective Date],
           [DocName],
           [Submitted]
    FROM [dbo].[InsuranceClaims]
) AS SourceTable PIVOT(AVG([Submitted]) FOR [DocName] IN([Doc A],
                                                         [Doc B],
                                                         [Doc C],
                                                         [Doc D],
                                                         [Doc E])) AS PivotTable;

An Efficient Way to Turn Rows into Columns to Check the Revenue of Products under Each Month

1 Answers1