How can I pivot this

Question

I'm trying to pivot some info and can't resolve it. Please help me. I'm using SQL Express 2012. I wrote this example:

CREATE TABLE #temp
(
    Mes varchar(2),
    Qty int,
    Modelo varchar(50)
)
insert into #temp values('01', 11, 'TC')
insert into #temp values('01', 21, 'TC2')
insert into #temp values('01', 22, 'Plus2')
insert into #temp values('02', 12, 'TC')
insert into #temp values('02', 22, 'TC2')
insert into #temp values('02', 32, 'Plus2')
insert into #temp values('03', 13, 'TC')
insert into #temp values('03', 23, 'TC2')
insert into #temp values('03', 33, 'Plus2')

SELECT Modelo, [AAA] as Mes1, [BBB] as Mes2, [CCC] as Mes3
from 
(
    select Mes, Modelo, Qty
    from #temp
) x
PIVOT
(
   SUM(Qty)
   FOR Mes IN([AAA], [BBB], [CCC])
) as p

drop table #temp

The result for this is:

 Modelo | Mes1  |  Mes2  |  Mes3
-------------------------------
 Plus2  | NULL  |  NULL  |  NULL
 TC     | NULL  |  NULL  |  NULL
 TC2    | NULL  |  NULL  |  NULL

I have 2 problems here, first one no result in the intersection Model (Modelo in rows) and Month (Mes) in columns (aaa, bbb, ccc)

The second one, is how to put the month (Mes) name in the column directly without using as Mes1, etc.

Thank you for any help.

Answer 1

You inserted ('01', '02', '03') in Mes column. Values like 'AAA', 'BBB' and 'CCC' are invalid values and will return null. So you must chenge your code as follow:

CREATE TABLE #temp
(
    Mes varchar(2),
    Qty int,
    Modelo varchar(50)
)
insert into #temp values('01', 11, 'TC')
insert into #temp values('01', 21, 'TC2')
insert into #temp values('01', 22, 'Plus2')
insert into #temp values('02', 12, 'TC')
insert into #temp values('02', 22, 'TC2')
insert into #temp values('02', 32, 'Plus2')
insert into #temp values('03', 13, 'TC')
insert into #temp values('03', 23, 'TC2')
insert into #temp values('03', 33, 'Plus2')

SELECT Modelo, [01] as Mes1, [02] as Mes2, [03] as Mes3
from 
(
    select Mes, Modelo, Qty
    from #temp
) x
PIVOT
(
   SUM(Qty)
   FOR Mes IN([01], [02], [03])
) as p
drop table #temp

It will return:

Modelo  | Mes1  | Mes2  | Mes3
--------+-------+-------+-----------
Plus2   | 22    | 32    | 33
TC      | 11    | 12    | 13
TC2     | 21    | 22    | 23

Answer 2

If you are talking abouth the 12 months of the year, you can do like this:

SELECT 
T1.Modelo
,   MAX(CASE WHEN T1.Mes = '01' THEN T1.Qty ELSE NULL END)      AS [JAN]
,   MAX(CASE WHEN T1.Mes = '02' THEN T1.Qty ELSE NULL END)      AS [FEB]
,   MAX(CASE WHEN T1.Mes = '03' THEN T1.Qty ELSE NULL END)      AS [MAR]
,   MAX(CASE WHEN T1.Mes = '04' THEN T1.Qty ELSE NULL END)      AS [APR]
,   MAX(CASE WHEN T1.Mes = '05' THEN T1.Qty ELSE NULL END)      AS [MAY]
,   MAX(CASE WHEN T1.Mes = '06' THEN T1.Qty ELSE NULL END)      AS [JUN]
,   MAX(CASE WHEN T1.Mes = '07' THEN T1.Qty ELSE NULL END)      AS [JUL]
,   MAX(CASE WHEN T1.Mes = '08' THEN T1.Qty ELSE NULL END)      AS [AGO]
,   MAX(CASE WHEN T1.Mes = '09' THEN T1.Qty ELSE NULL END)      AS [SEP]
,   MAX(CASE WHEN T1.Mes = '10' THEN T1.Qty ELSE NULL END)      AS [OCT]
,   MAX(CASE WHEN T1.Mes = '11' THEN T1.Qty ELSE NULL END)      AS [NOV]
,   MAX(CASE WHEN T1.Mes = '12' THEN T1.Qty ELSE NULL END)      AS [DEC]
FROM #temp T1
GROUP BY T1.Modelo

See working exemple: SQL Fiddle