following is the result of my query
Year_Month.........cat1_per......cat2_per........cat3_per......cat4_per
2004_06...............0.892..........0.778............0.467..........0.871
2005_10...............0.790..........0.629............0.581..........0.978
but i want output of my query to be
Category...........2004_06..............2005_10
cat1_per.............0.892..................0.790
cat2_per.............0.778..................0.629
cat3_per.............0.467..................0.581
cat4_per.............0.871..................0.978
what is the best way to do it? any help is appreciated.
I tried Unpivot but was not able to get desired result.
You did not specify the RDBMS but since you tagged it tsql I am guessing sql-server. This is actually both an UNPIVOT and then a PIVOT. If you know the values that you need to transform, then you can hard-code them via a static version:
select *
from
(
select year_month, value, category
from table1
unpivot
(
value
for category in (cat1_per, cat2_per, cat3_per, cat4_per)
) un
) x
pivot
(
max(value)
for year_month in ([2004_06], [2005_10])
)p
See SQL Fiddle With Demo
If you then have a unknown number of values to transform, then you can use a dynamic sql pivot:
DECLARE @colsUnpivot AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@colsPivot as NVARCHAR(MAX)
select @colsUnpivot = stuff((select ','+quotename(C.name)
from sys.columns as C
where C.object_id = object_id('Table1') and
C.name like 'cat%per'
for xml path('')), 1, 1, '')
select @colsPivot = STUFF((SELECT ','
+ quotename(t.year_month)
from Table1 t
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query
= 'select *
from
(
select year_month, value, category
from table1
unpivot
(
value
for category in ('+ @colsunpivot +')
) un
) x
pivot
(
max(value)
for year_month in ('+ @colspivot +')
) p'
exec(@query)
See SQL Fiddle with demo
Both will produce the same results:
| CATEGORY | 2004_06 | 2005_10 |
--------------------------------
| cat1_per | 0.892 | 0.79 |
| cat2_per | 0.778 | 0.629 |
| cat3_per | 0.467 | 0.581 |
| cat4_per | 0.871 | 0.978 |
If for some reason, you did not have the UNPIVOT and PIVOT functions then you can replicate this using a combination of UNION ALL to unpivot and then a CASE statement and an aggregate function to pivot:
select category,
max(case when year_month = '2004_06' then value end) [2004_06],
max(case when year_month = '2005_10' then value end) [2005_10]
from
(
select year_month, cat1_per value, 'cat1_per' category
from table1
union all
select year_month, cat2_per value, 'cat2_per' category
from table1
union all
select year_month, cat3_per value, 'cat3_per' category
from table1
union all
select year_month, cat4_per value, 'cat4_per' category
from table1
) un
group by category
See SQL Fiddle with Demo
