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

pepperbr

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

Frank Kulash

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

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

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

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