Not able to Pivot in SQL

Question

Below is my table Brand Year

 Brand   year   Tag        Jan   Feb  
------------------------------------- 
 Brand1  2017   Secondary  4     9     
 Brand1  2017   Primary    11    56     
 Brand1  2016   Secondary  0     2     

My Output should be as below:

 Brand   year   Month   Secondary   Primary
--------------------------------------------
 Brand1  2017   Jan     4           11
 Brand1  2017   Feb     9           56
 Brand1  2016   Jan     0           NULL
 Brand1  2016   Feb     2           NULL

I want this through SQL

Answer 1

Slightly modified from Yogesh Sharma:

select brand,year,month,
max(case when a.tag='secondary' then [primary]  end) as secondary,
max(case when a.tag='primary' then [primary]  end) as [primary]

from
(select brand,
year,
tag,
'jan' as month,
 jan as [primary]
from pivot_int
union
select brand,
year,
tag,
'feb' as month,
 feb as secondary
 from pivot_int
 )a
 group by brand,year,month
 order by 2 desc,4 asc

http://sqlfiddle.com/#!18/57427/32

Saravanan

Answer 2

If you are working with SQL Server you could use apply operator

select t.Brand, t.year, a.Months,
       max(case when t.tag = 'Secondary' then a.Value end)  Secondary,
       max(case when t.tag = 'Primary  ' then a.Value end)  [Primary]
from table t
cross apply (values (Jan, 'Jan'), (Feb, 'Feb'))a(Value, Months)
group by t.Brand, t.year, a.Months
order by 2 desc, 4 asc

However, this above is doing both operations pivoting (i.e. you can see conditional aggregation with max() function) as well as unpivoting (i.e. cross apply) with