Forum Stats

  • 3,750,344 Users
  • 2,250,158 Discussions
  • 7,866,940 Comments

Discussions

Convert rows to columns where all data is TEXT (no aggregation)

pepperbr
pepperbr Member Posts: 1 Blue Ribbon

How do I convert name/value pair data in rows to columns?


Sample values in a query result set:

ID NAME VALUE

----- --------------- -----------------------

First Red Value1

First Blue Value2

First Green Value3

Third Red Value4

Third Blue Value5

Third Green Value6


Desired result set:

ID RED BLUE GREEN

------ ---------- --------- -----------

First Value1 Value2 Value3

Third Value4 Value5 Value6

Tagged:

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,887 Red Diamond
    edited Oct 9, 2020 1:35AM

    Hi,

    You can use SELECT ... PIVOT, like this:

    SELECT  *
    FROM   table_x
    PIVOT   ( MIN (value)
         FOR name IN ( 'Red'  AS red
                                 , 'Blue'  AS blue
                                 , 'Green' AS green
                                 )
                  )
    ORDER BY id -- If wanted
    ;
    

    If you'd care to post CREATE TABLE and INSERT rows for the sample data, then I could test this.

    In your subject line, you said "no aggregation", but aggregation means producing one row of output from a group of (potentially) several rows, and that's exactly what you asked to do.

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,778 Bronze Crown
    edited Oct 10, 2020 4:08AM

    With both using the PIVOT syntax and doing it the old way, you will need to use an agregate function to achieve what you want. Usually MAX() or MIN(), to achieve the desired result, even with TEXT. Using an aggregate function allows you to group by just the ID column, thus enabling the pivot of the data..

    Example:

    select id,
          max(case name when 'Red' then value end)  red,
          max(case name when 'Blue' then value end) blue,
          max(case name when 'Green' then value end) green
    from  nvp_tab
    group by id
    /
    
    ID   RED   BLUE  GREEN
    ----- ------ ------ ------
    First Value1 Value2 Value3
    Third Value4 Value5 Value6
    
  • mathguy
    mathguy Member Posts: 9,966 Gold Crown

    Here is one way that doesn't use aggregate functions:

    with
     sample_table (id, name, value) as (
       select 'First', 'Red' , 'Value1' from dual union all
       select 'First', 'Blue' , 'Value2' from dual union all
       select 'First', 'Green', 'Value3' from dual union all
       select 'Third', 'Red' , 'Value4' from dual union all
       select 'Third', 'Blue' , 'Value5' from dual union all
       select 'Third', 'Green', 'Value6' from dual
     )
    select id, red, blue, green
    from  sample_table
    match_recognize (
     partition by id
     measures red.value as red, blue.value as blue, green.value as green
     pattern  ( (red|blue|green|other)* )
     define   red as name = 'Red', blue as name = 'Blue', green as name = 'Green'
    )
    ;
    
    ID     RED      BLUE    GREEN 
    ----- ------   ------    ------
    First  Value1  Value2  Value3
    Third Value4 Value5 Value6
    


  • Ivan84F
    Ivan84F Member Posts: 2 Blue Ribbon

    An other way with sum(decode())

    select id, max(decode(name,'Red',value,NULL)) RED,max(decode(name,'Blue',value,NULL)) BLUE,max(decode(name,'Green',value,NULL)) GREEN

    from sample_table

    group by id

    ID   RED   BLUE  GREEN

    ----- ------ ------ ------

    Third Value4 Value5 Value6

    First Value1 Value2 Value3