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 Go to next message
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 #644838 is a reply to message #644837] Wed, 18 November 2015 09:57 Go to previous messageGo to next message
tigsav
Messages: 49
Registered: April 2012
Member
How about listagg and then using a combination of Substr and instr to obtain the reqd format?
Re: Convert Rows to Columns [message #644840 is a reply to message #644837] Wed, 18 November 2015 10:29 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3267
Registered: January 2010
Location: Connecticut, USA
Senior Member
uman2631 wrote on Wed, 18 November 2015 10:54
Pivot 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:54
Over 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 #644841 is a reply to message #644838] Wed, 18 November 2015 10:36 Go to previous messageGo to next message
uman2631
Messages: 16
Registered: November 2011
Junior Member
I never considered adding max() and group by. This is the solution I needed:

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 CHOICE_TBL
group by person
Order by Person;
Re: Convert Rows to Columns [message #644842 is a reply to message #644841] Wed, 18 November 2015 11:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

PIVOT will have better performances but only applies on 11g and up (which is your case).
MAX(DECODE (or CASE) is the old way to do it in previous versions.

Re: Convert Rows to Columns [message #644846 is a reply to message #644842] Wed, 18 November 2015 13:20 Go to previous messageGo to next message
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 #644847 is a reply to message #644846] Wed, 18 November 2015 13:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Laughing

Re: Convert Rows to Columns [message #644977 is a reply to message #644847] Mon, 23 November 2015 01:33 Go to previous message
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;
Previous Topic: Need Explanation on this query
Next Topic: Sysdate to_char DAY conversion not comparable
Goto Forum:
  


Current Time: Tue Mar 19 02:15:04 CDT 2024