Reputation: 77
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.
Upvotes: 0
Views: 65
Reputation: 485
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
Upvotes: 1
Reputation: 426
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
Upvotes: 0