Home » SQL & PL/SQL » SQL & PL/SQL » Transpose rows to columns without an aggregation (Oracle 11g)
| Transpose rows to columns without an aggregation [message #632358] |
Thu, 29 January 2015 07:18  |
rangan.s
Messages: 75
Registered: February 2008
Location: chennai
|
Member |
|
|
Hi,
I am trying to transpose a dataset without applying the aggregation.
The sample data in table is :
Qtr Sub1 Sub2 Sub3 Sub4 Sub5
01-14 50 50 50 50 50
02-14 45 45 54 54 45
03-14 98 95 95 95 95
04-14 75 75 75 75 75
01-15 65 65 65 65 85
Expected values :
Qtr 01-14 02-14 03-14 04-14 01-15
Sub1 50 45 98 75 65
Sub2 50 45 95 75 65
Sub3 50 54 95 75 65
Sub4 50 54 95 75 65
Sub5 50 45 95 75 85
Is there anyway to achieve this in oracle SQL.
|
|
|
|
|
|
| Re: Transpose rows to columns without an aggregation [message #632368 is a reply to message #632360] |
Thu, 29 January 2015 08:43  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
@OP,
without aggregation, but still in SQL? Then why not use a Microsoft Excel sheet, copy paste the contents, and use TRANSPOSE while pasting. I am not joking, I just want to know why to do it in SQL. And what exactly is stopping you to use aggregation.
|
|
|
|
|
|
| Re: Transpose rows to columns without an aggregation [message #632388 is a reply to message #632374] |
Thu, 29 January 2015 12:05 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
We need to know QTR list, no matter what. Then all we need is LEAD/LAG:
with t1 as (
select '01-14' qtr,50 sub1,50 sub2,50 sub3,50 sub4,50 sub5 from dual union all
select '02-14',45,45,54,54,45 from dual union all
select '03-14',98,95,95,95,95 from dual union all
select '04-14',75,75,75,75,75 from dual union all
select '01-15',65,65,65,65,85 from dual
),
t2 as (
select t1.*,
row_number() over(order by substr(qtr,-2),qtr) rn
from t1
),
t3 as (
select 'Sub' || rn "Qtr",
case rn
when 1 then sub1
when 2 then lag(sub2) over(order by rn)
when 3 then lag(sub3,2) over(order by rn)
when 4 then lag(sub4,3) over(order by rn)
else lag(sub5,4) over(order by rn)
end "01-14",
case rn
when 1 then lead(sub1) over(order by rn)
when 2 then sub2
when 3 then lag(sub3) over(order by rn)
when 4 then lag(sub4,2) over(order by rn)
else lag(sub5,3) over(order by rn)
end "02-14",
case rn
when 1 then lead(sub1,2) over(order by rn)
when 2 then lead(sub2) over(order by rn)
when 3 then sub3
when 4 then lag(sub4) over(order by rn)
else lag(sub5,2) over(order by rn)
end "03-14",
case rn
when 1 then lead(sub1,3) over(order by rn)
when 2 then lead(sub2,2) over(order by rn)
when 3 then lead(sub3) over(order by rn)
when 4 then sub4
else lag(sub5) over(order by rn)
end "04-14",
case rn
when 1 then lead(sub1,4) over(order by rn)
when 2 then lead(sub2,3) over(order by rn)
when 3 then lead(sub3,2) over(order by rn)
when 4 then lead(sub4) over(order by rn)
else sub5
end "01-15",
rn
from t2
)
select "Qtr",
"01-14",
"02-14",
"03-14",
"04-14",
"01-15"
from t3
where rn <= 5
/
Qtr 01-14 02-14 03-14 04-14 01-15
---- ---------- ---------- ---------- ---------- ----------
Sub1 50 45 98 75 65
Sub2 50 45 95 75 65
Sub3 50 54 95 75 65
Sub4 50 54 95 75 65
Sub5 50 45 95 75 85
SQL>
SY.
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 23:49:47 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
