Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Convert rows to columns where all data is TEXT (no aggregation)

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
Answers
-
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.
-
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
-
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
-
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