PIVOT or UNPIVOT: Will either work?

Question

I have looked around for examples that are specifically like what I have but I just can't find anything. From what I've seen I'm not sure it's possible but I figured I'd ask here first.

1. Original Table

I have a table that looks like this:

+-------------+------+----------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
| Application | Year | YearTarg | JanTarg | FebTarg | MarTarg | AprTarg | MayTarg | JunTarg | JulTarg | AugTarg | SepTarg | OctTarg | NovTarg | DecTarg |
+-------------+------+----------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
| Mod         | 2018 |  5700000 |  400000 |  300000 |  400000 |  600000 |  500000 |  500000 |  600000 |  500000 |  500000 |  600000 |  500000 |  400000 |
| RC          | 2018 |  1700000 |   40000 |   50000 |   60000 |   80000 |  100000 |  120000 |  150000 |  180000 |  220000 |  250000 |  220000 |  230000 |
| Flow        | 2018 |  2000000 |   30000 |   50000 |   20000 |   80000 |  250000 |   80000 |   70000 |  200000 |  300000 |  350000 |  200000 |  190000 |
| Non-RA      | 2018 | 13400000 | 1100000 |  900000 | 1000000 | 1200000 | 1000000 | 1100000 | 1100000 | 1000000 | 1100000 | 1500000 | 1100000 | 1300000 |
| AMH         | 2018 |  3500000 |  100000 |  100000 |  300000 |  200000 |  500000 |  400000 |  300000 |  500000 |  200000 |  200000 |  400000 |  300000 |
+-------------+------+----------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+

2. New Table

I'd like to get the results back looking like this:

I'm really struggling, and I think part of it is the inclusion of the number 1 to represent January's numbers, 2 for February's numbers, and so on.

I need this functionality in order to join back to another table based on the month value i.e. NewTable.Month = OtherTable.InvMonth (which is just MONTH(GETDATE())).

Can this be done?

score 1 · Accepted Answer · answered Jun 21 '18 at 23:52

1

Unpivot would work, but I think a CROSS APPLY is easier and offers a bit more flexibility.

Example

Select A.Application
      ,A.Year
      ,B.*
 From  YourTable A
 Cross Apply ( values (1,JanTarg)
                     ,(2,FebTarg)
                     ,(3,MarTarg)
                     ,(4,AprTarg)
                     ,(5,MayTarg)
                     ,(6,JunTarg)
                     ,(7,JulTarg)
                     ,(8,AugTarg)
                     ,(9,SepTarg)
                     ,(10,OctTarg)
                     ,(11,NovTarg)
                     ,(12,DecTarg)
             ) B ([Month],[Target])

answered Jun 21 '18 at 23:52

John Cappelletti

79,615
7
44
66

That did the trick. Thank you SO much! Now I have to go learn about CROSS APPLY. I feel like maybe I've heard about it in the past, but I know nothing about it. Thanks again! – B.Scar Jun 22 '18 at 16:48
1

@B.Scar Always happy to help. Well worth your time to look into CROSS APPLY vs. OUTER APPLY – John Cappelletti Jun 22 '18 at 16:57

score 1 · Answer 2 · answered Jun 22 '18 at 08:13

If you prefer to use UNPIVOT:

declare @tmp table([Application] varchar(50), [Year] int, [YearTarg] int, [JanTarg] int, [FebTarg] int, [MarTarg] int, [AprTarg] int, [MayTarg] int, [JunTarg] int, [JulTarg] int, [AugTarg] int, [SepTarg] int, [OctTarg] int, [NovTarg] int, [DecTarg] int)

insert into @tmp values
('Mod'   , 2018, 5700000, 400000, 300000, 400000, 600000, 500000, 500000, 600000, 500000, 500000, 600000, 500000, 400000),
('RC'    , 2018, 1700000,  40000,  50000,  60000,  80000, 100000, 120000, 150000, 180000, 220000, 250000, 220000, 230000),
('Flow'  , 2018, 2000000,  30000,  50000,  20000,  80000, 250000,  80000,  70000, 200000, 300000, 350000, 200000, 190000),
('Non-RA', 2018,13400000,1100000, 900000,1000000,1200000,1000000,1100000,1100000,1000000,1100000,1500000,1100000,1300000),
('AMH'   , 2018, 3500000, 100000, 100000, 300000, 200000, 500000, 400000, 300000, 500000, 200000, 200000, 400000, 300000)

select u.[Application], 
       u.[Year],
       case when u.[Month] ='JanTarg' then 1 when u.[Month] ='FebTarg' then 2 when u.[Month] ='MarTarg' then 3 when u.[Month] ='AprTarg' then 4 when u.[Month] ='MayTarg' then 5 when u.[Month] ='JunTarg' then 6 when u.[Month] ='JulTarg' then 7 when u.[Month] ='AugTarg' then 8 when u.[Month] ='SepTarg' then 9 when u.[Month] ='OctTarg' then 10 when u.[Month] ='NovTarg' then 11 when u.[Month] ='DecTarg' then 12 end as [Month],
       u.[Target]
from @tmp s 
unpivot 
( 
    [Target] 
    for [Month] in ([JanTarg],[FebTarg],[MarTarg],[AprTarg],[MayTarg],[JunTarg],[JulTarg],[AugTarg],[SepTarg],[OctTarg],[NovTarg],[DecTarg]) 
) u;

Results:

PIVOT or UNPIVOT: Will either work?

2 Answers2