This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,699 Users
  • 2,269,776 Discussions


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

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:


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

First Red Value1

First Blue Value2

First Green Value3

Third Red Value4

Third Blue Value5

Third Green Value6

Desired result set:


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

First Value1 Value2 Value3

Third Value4 Value5 Value6



  • Frank Kulash
    Frank Kulash Boston, USAMember, Moderator Posts: 43,002 Red Diamond
    edited Oct 9, 2020 1:35AM


    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,785 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..


    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
    ----- ------ ------ ------
    First Value1 Value2 Value3
    Third Value4 Value5 Value6
  • mathguy
    mathguy Member Posts: 11,041 Black Diamond

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

     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


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

    Third Value4 Value5 Value6

    First Value1 Value2 Value3