Select query for Converting Oracle Rows to Columns

Question

I have the below table

ID-----Name------Value
1      City      New York
1      Country   USA
2      City      Barcelona
2      Country   Spain

Desired query output is

ID------City-------Country
1       New York   USA
2       Barcelona  Spain

Could you please let me know if this can be achieved using a query in Oracle. I came across two concepts listagg and pivote but not able to understand how to use those to achieve what I need.

I am getting the result in one way using listagg with the below query but all columns are coming in single column which I need to separate out.

select ID, 
       listagg(Name||';'||Value, ',') within group (order by Name) as Criteria
from table_name
group by ID;

Gordon Linoff · Accepted Answer · 2017-09-20 18:49:57Z

3

I would do it using aggregation:

select id, max(case when name = 'City' then value end) as city,
       max(case when name = 'Country' then value end) as country
from t
group by id;

answered Sep 20, 2017 at 18:49

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vinay Over a year ago

Thank you very much for the quick response Gordon Linoff, this fulfills the purpose I am looking for. Also can you please explain the reason of having max for my better understanding.

Gordon Linoff Over a year ago

@Vinay . . . It is needed for the group by. There should only be one value, so the max() just chooses it.

MT0 · Accepted Answer · 2017-09-20 19:18:50Z

3

Use PIVOT:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE countries ( ID, Name, Value ) AS
  SELECT 1, 'City',    'New York' FROM DUAL UNION ALL
  SELECT 1, 'Country', 'USA' FROM DUAL UNION ALL
  SELECT 2, 'City',    'Barcelona' FROM DUAL UNION ALL
  SELECT 2, 'Country', 'Spain' FROM DUAL;

Query 1:

SELECT *
FROM   countries
PIVOT  ( MAX( value ) FOR name IN ( 'City' AS City, 'Country' AS Country ) )

Results:

| ID |      CITY | COUNTRY |
|----|-----------|---------|
|  1 |  New York |     USA |
|  2 | Barcelona |   Spain |

answered Sep 20, 2017 at 19:18

MT0

173k12 gold badges70 silver badges136 bronze badges

Collectives™ on Stack Overflow

Select query for Converting Oracle Rows to Columns

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related