Forum Stats

  • 3,851,702 Users
  • 2,264,013 Discussions
  • 7,904,820 Comments

Discussions

Categories

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

pepperbr
pepperbr Member Posts: 1 Blue Ribbon
in SQL & PL/SQL

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:
· Share on TwitterShare on Facebook

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,477 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.

    · Share on TwitterShare on Facebook
  • 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..

    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
    · Share on TwitterShare on Facebook
  • mathguy
    mathguy Member Posts: 10,670 Blue Diamond

    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


    · Share on TwitterShare on Facebook
  • 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

    · Share on TwitterShare on Facebook