Home » SQL & PL/SQL » SQL & PL/SQL » Convert Rows to Columns (Oracle, 11g, Solaris)
Convert Rows to Columns [message #644837] |
Wed, 18 November 2015 09:54 |
|
uman2631
Messages: 16 Registered: November 2011
|
Junior Member |
|
|
Trying to convert rows to columns. Pivot doesnt seem to apply because I am not aggregating. Over doesnt seem to apply either, because I am not lagging or leading.
CHOICE_TBL Table looks like this:
PERSON CHOICE_NBR COLOR
Jones 1 Yellow
Jones 2 Green
Jones 3 Blue
Smith 1 Orange
What I want is this:
PERSON CHOICE_NBR1 CHOICE_NBR2 CHOICE_NBR3
Jones Yellow Green Blue
Smith Orange
The code I came up with so far is:
select person,
(case when choice_nbr=1 THEN color end) choice_nbr1,
(case when choice_nbr=2 THEN color end) choice_nbr2,
(case when choice_nbr=3 THEN color end) choice_nbr3
from CHOICE_TBL
Order by Person;
But that yields:
PERSON CHOICE_NBR1 CHOICE_NBR2 CHOICE_NBR3
Jones Yellow <NULL> <NULL>
Jones <NULL> Green <NULL>
Jones <NULL> <NULL> Blue
Smith Orange
I know I'm close, but how do I do this?
[Updated on: Wed, 18 November 2015 09:57] Report message to a moderator
|
|
|
|
Re: Convert Rows to Columns [message #644840 is a reply to message #644837] |
Wed, 18 November 2015 10:29 |
Solomon Yakobson
Messages: 3267 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
uman2631 wrote on Wed, 18 November 2015 10:54Pivot doesnt seem to apply because I am not aggregating.
Really?
SQL> with choice_tbl as (
2 select 'Jones' person,1 choice_nbr,'Yellow' color from dual union all
3 select 'Jones',2,'Green' from dual union all
4 select 'Jones',3,'Blue' from dual union all
5 select 'Smith',1,'Orange' from dual
6 )
7 select *
8 from choice_tbl
9 pivot(
10 max(color)
11 for choice_nbr in (1 choice_nbr1,2 choice_nbr2,3 choice_nbr3)
12 )
13 /
PERSO CHOICE_NBR1 CHOICE_NBR2 CHOICE_NBR3
----- ----------- ----------- -----------
Jones Yellow Green Blue
Smith Orange
SQL>
uman2631 wrote on Wed, 18 November 2015 10:54Over doesnt seem to apply either
SQL> with choice_tbl as (
2 select 'Jones' person,1 choice_nbr,'Yellow' color from dual union all
3 select 'Jones',2,'Green' from dual union all
4 select 'Jones',3,'Blue' from dual union all
5 select 'Smith',1,'Orange' from dual
6 ),
7 t as (
8 select person,
9 case choice_nbr
10 when 1 then color
11 when 2 then lag(color) over(partition by person order by choice_nbr)
12 else lag(color,2) over(partition by person order by choice_nbr)
13 end choice_nbr1,
14 case choice_nbr
15 when 1 then lead(color) over(partition by person order by choice_nbr)
16 when 2 then color
17 else lag(color) over(partition by person order by choice_nbr)
18 end choice_nbr2,
19 case choice_nbr
20 when 1 then lead(color,2) over(partition by person order by choice_nbr)
21 when 2 then lead(color) over(partition by person order by choice_nbr)
22 else color
23 end choice_nbr3,
24 row_number() over(partition by person order by choice_nbr) rn
25 from choice_tbl
26 )
27 select person,
28 choice_nbr1,
29 choice_nbr2,
30 choice_nbr3
31 from t
32 where rn = 1
33 /
PERSO CHOICE_NBR1 CHOICE_NBR2 CHOICE_NBR3
----- ----------- ----------- -----------
Jones Yellow Green Blue
Smith Orange
SQL>
Aggregate solution:
SQL> with choice_tbl as (
2 select 'Jones' person,1 choice_nbr,'Yellow' color from dual union all
3 select 'Jones',2,'Green' from dual union all
4 select 'Jones',3,'Blue' from dual union all
5 select 'Smith',1,'Orange' from dual
6 )
7 select person,
8 max(
9 case choice_nbr
10 when 1 then color
11 end
12 ) choice_nbr1,
13 max(
14 case choice_nbr
15 when 2 then color
16 end
17 ) choice_nbr2,
18 max(
19 case choice_nbr
20 when 3 then color
21 end
22 ) choice_nbr3
23 from choice_tbl
24 group by person
25 /
PERSO CHOICE_NBR1 CHOICE_NBR2 CHOICE_NBR3
----- ----------- ----------- -----------
Jones Yellow Green Blue
Smith Orange
SQL>
SY.
[Updated on: Wed, 18 November 2015 10:31] Report message to a moderator
|
|
|
|
|
Re: Convert Rows to Columns [message #644846 is a reply to message #644842] |
Wed, 18 November 2015 13:20 |
Solomon Yakobson
Messages: 3267 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Michel Cadot wrote on Wed, 18 November 2015 12:31
PIVOT will have better performances
Common misperception. Under the hood PIVOT is same MAX + CASE. You can easily check it in 12C where Oracle added EXPAND_SQL_TEXT procedure to DBMS_UTILITY package (I formatted output for readability):
SQL> variable c clob
SQL> begin
2 dbms_utility.expand_sql_text(Q'[with choice_tbl as (
3 select 'Jones' person,1 choice_nbr,'Yellow' color from dual union all
4 select 'Jones',2,'Green' from dual union all
5 select 'Jones',3,'Blue' from dual union all
6 select 'Smith',1,'Orange' from dual
7 )
8 select *
9 from choice_tbl
10 pivot(
11 max(color)
12 for choice_nbr in (1 choice_nbr1,2 choice_nbr2,3 choice_nbr3)
13 )]',:c);
14 end;
15 /
PL/SQL procedure successfully completed.
SQL> set long 100000
SQL> print c
C
--------------------------------------------------------------------------------
SELECT "A1"."PERSON" "PERSON",
"A1"."CHOICE_NBR1" "CHOICE_NBR1",
"A1"."CHOICE_NBR2" "CHOICE_NBR2",
"A1"."CHOICE_NBR3" "CHOICE_NBR3"
FROM (
SELECT "A2"."PERSON" "PERSON",
MAX(CASE WHEN ("A2"."CHOICE_NBR"=1) THEN "A2"."COLOR" END ) "CHOICE_NBR1",
MAX(CASE WHEN ("A2"."CHOICE_NBR"=2) THEN "A2"."COLOR" END ) "CHOICE_NBR2",
MAX(CASE WHEN ("A2"."CHOICE_NBR"=3) THEN "A2"."COLOR" END ) "CHOICE_NBR3"
FROM (
(SELECT 'Jones' "PERSON",1 "CHOICE_NBR",'Yellow' "COLOR" FROM "SYS"."DUAL" "A7") UNION ALL
(SELECT 'Jones' "'JONES'",2 "2",'Green' "'GREEN'" FROM "SYS"."DUAL" "A6") UNION ALL
(SELECT 'Jones' "'JONES'",3 "3",'Blue' "'BLUE'" FROM "SYS"."DUAL" "A5") UNION ALL
(SELECT 'Smith' "'SMITH'",1 "1",'Orange' "'ORANGE'" FROM "SYS"."DUAL" "A4")
) "A2"
GROUP BY "A2"."PERSON"
) "A1"
SQL>
SY.
|
|
|
|
Re: Convert Rows to Columns [message #644977 is a reply to message #644847] |
Mon, 23 November 2015 01:33 |
|
Rishab_le_noob
Messages: 12 Registered: November 2015 Location: Kolkata
|
Junior Member |
|
|
Yup same solution by me (without checking the answers)
with dte as (
select 'Jones' PERSON, 1 CHOICE_NBR, 'Yellow' COLOR from dual union all
select 'Jones', 2 ,'Green' from dual union all
select 'Jones', 3 ,'Blue' from dual union all
select 'Smith', 1 ,'Orange' from dual )
select PERSON,
max(case when CHOICE_NBR=1 then COLOR end) CHOICE_NBR1,
max(case when CHOICE_NBR=2 then COLOR end) CHOICE_NBR2,
max(case when CHOICE_NBR=3 then COLOR end) CHOICE_NBR3
from dte
group by PERSON;
|
|
|
Goto Forum:
Current Time: Tue Mar 19 02:15:04 CDT 2024
|