SQL How to pivot two columns of data into different columns?

Question

This is the table I have:

| Scheme Code | MonthYear | Revenue | Revenue2 |
|-------------|-----------|---------|----------|
| 18VDA       | 2018.1    | 100     | 50       |
| 18VDA       | 2018.2    | 200     | 100      |
| 18VDA       | 2018.3    | 200     | 150      |

and I want to pivot it to like this:

| Scheme Code | 2018.1 A | 2018.2 A | 2018.3 A | 2018.1 B | 2018.2 B | 2018.3 B |
|-------------|----------|----------|----------|----------|----------|----------|
| 18VDA       | 100      | 200      | 200      | 50       | 100      | 150      |

How do I do it so that it pivots in MonthYear, but it duplicates it for both Revenue and Revenue2?

Thanks

EDIT: Messed up the output table I was hoping for! I've edited the actual output table I want to see!

EDIT 2:

DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)

Select @cols = STUFF((SELECT ',' + QUOTENAME([MonthYear]) 
                    from tableA
                    group by [MonthYear]
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT *
             FROM ( SELECT [Scheme Code], MonthYear ,[Revenue]
                    FROM TableA
                    ) a
              PIVOT(sum(Revenue) for MonthYear in (' + @cols + ') 
                       ) as RevenueMonth
              ORDER BY [Scheme Code]'    

 execute(@query);

This code I wrote will do it for just one column, and I get the output like this:

| Scheme Code | 2018.1 | 2018.2 | 2018.3 |
|-------------|--------|--------|--------|
| 18VDA       | 100    | 200    | 200    |

Note: 2018.3 A is 300 in the result table, but the input table is 200. Typo? — Paul Williams, Jun 28 '19 at 14:12
I edited the output table of the table I actually want as output, the previous table was the wrong one by accident! — digeridoo, Jun 28 '19 at 14:34
Check out the `PIVOT` operator https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-2017 — tim, Jun 28 '19 at 16:20

score 8 · Answer 1 · answered Jun 28 '19 at 16:28

My suggestion always is to try to write your query as a hard-coded or static version first before diving into dynamic SQL. This let's you get the final result you want with a smaller subset of data and you can verify that you have the logic correct.

I would tackle this by performing an UNPIVOT of the two Revenue columns first, then look at applying the PIVOT function. To UNPIVOT you can use either the UNPIVOT function or you can use CROSS APPLY with a UNION ALL to convert your two Revenue columns into a single column. A static version of the query would be similar to this:

select *
from
(
    select 
        t.[Scheme Code],
        new_colname = concat(t.[MonthYear], ' ', r.colname),
        r.colvalue
    from yourtable t
    cross apply
    (
        select 'A', Revenue union all
        select 'B', Revenue2
    ) r (colname, colvalue)
) d
pivot 
(
    sum(colvalue)
    for new_colname in ([2018.1 A], [2018.2 A], [2018.3 A], [2018.1 B], [2018.2 B], [2018.3 B])
) p;

You'll notice that in the CROSS APPLY I added a column with the A or B that I use to identify either the Revenue or Revenue2 columns. This is then used to create the new column names for the PIVOT.

This should generate the result you want. Now to do this dynamically, you just need to convert the SQL to dynamic code. You can use the following to get the result:

DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)

Select @cols = STUFF((SELECT ',' + QUOTENAME(concat([MonthYear], x.col)) 
                    from yourtable
                    cross join (select col = ' A' union all select ' B') x
                    group by [MonthYear], x.col
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT *
             FROM 
             ( 
                select 
                    t.[Scheme Code],
                    new_colname = concat(t.[MonthYear], '' '', r.colname),
                    r.colvalue
                from yourtable t
                cross apply
                (
                    select ''A'', Revenue union all
                    select ''B'', Revenue2
                ) r (colname, colvalue)
              ) a
              PIVOT
              (
                sum(colvalue) for new_colname in (' + @cols + ') 
              ) as x
              ORDER BY [Scheme Code]';

exec sp_executesql @query;

Both of these should generate the same results (dbfiddle demo)

hello sir, can you helping me with this question https://stackoverflow.com/questions/62652178/using-pivot-function-in-mysql-to-make-a-table-apriori — 18Man, Jun 30 '20 at 09:12

Serg · Answer 2 · 2019-06-28T15:38:49.433

0

Do it with CASE and dynamic sql.

DECLARE @colsA AS NVARCHAR(MAX),
        @colsB AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX)

select @colsA = (SELECT ', sum(case [MonthYear] when ''' + [MonthYear] + ''' then Revenue end)' + QUOTENAME([MonthYear] + ' A')
                    from tableA
                    group by [MonthYear]
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'),
       @colsB = (SELECT ', sum(case [MonthYear] when ''' + [MonthYear] + ''' then Revenue2 end)' + QUOTENAME([MonthYear] + ' B')
                    from tableA
                    group by [MonthYear]
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)');

Set @query = 'select [Scheme Code]' + @colsA + @colsB + ' from  TableA  group by [Scheme Code] order by [Scheme Code];';
print @query;

edited Jun 28 '19 at 15:38

answered Jun 28 '19 at 14:21

Serg

22,285
5
21
48

Sorry, I edited the actual output table I am looking for. – digeridoo Jun 28 '19 at 14:31
Also, just so you know this is just an example table. I have plenty of dates that range from 2013.11 all the way till 2019.6 so making cases for all of them would be way too much hardcoding. – digeridoo Jun 28 '19 at 14:32
@digeridoo, then you need a dynamic sql. Search for sql dynamic pivot, a lot of answers exists. – Serg Jun 28 '19 at 14:37
I've been able to do it for just Revenue, but I'm not sure how to do it for Revenue2 as well where it duplicates MonthYear for both fields using dynamic sql. – digeridoo Jun 28 '19 at 14:39
Please show your code. Essentially you need to generate another set of columns very much similar to what you've done for Revenue – Serg Jun 28 '19 at 14:49
I've made an EDIT2 with the code and the output table I get, if you want to take a look at that. This only does it for one column though (Revenue) but I want both Revenue and Revenue2. – digeridoo Jun 28 '19 at 14:58
It says "Msg 241, Level 16, State 1, Line 5 Conversion failed when converting date and/or time from character string." – digeridoo Jun 28 '19 at 15:53
The answer relies on MonthYear column format as it is shown in the question. Convert real value to this format first. – Serg Jun 28 '19 at 15:57

SQL How to pivot two columns of data into different columns?

2 Answers2