Transform subtables in the same table to columns

Question

I have a table like this:

ID	City	Param	Value
id1	city1	a	value
id2	city1	b	value
id3	city1	c	value
id4	city2	a	value
id5	city2	b	value
id6	city2	c	value
...	...	...	...
idN	cityN	a	value
idN+1	cityN	b	value
idN+2	cityN	c	value

As you can see it have a subtable for each city like:

Param	Value
a	value
b	value
c	value

So I would like to join all subtables and get a table like this:

Param	city1	city2	...	cityN
a	value	value	...	value
b	value	value	...	value
c	value	value	...	value

Any ideas how I can get it?

Thank you in advance!

Note1: The number of cities is variable.

Note2: The solution could be a PL/SQL function.

Answer 1

In order to get the result that you want you need to pivot the data. While you said plsql could be an option, you did not specify what version of Oracle you are using. Starting in Oracle 11g, the PIVOT function was made available.

If you are not using Oracle 119, then you can use an aggregate function with a CASE expression:

select param,
    max(case when city = 'city1' then value end) City1,
    max(case when city = 'city2' then value end) City2
from yourtable
group by param

See SQL Fiddle with Demo.

Since you state that you will have an unknown number of city values, then you will need to create a dynamic sql version of this query. You can create a procedure similar to this:

CREATE OR REPLACE procedure dynamic_pivot(p_cursor in out sys_refcursor)
as
    sql_query varchar2(1000) := 'select param ';

    begin
        for x in (select distinct city from yourtable order by 1)
        loop
            sql_query := sql_query ||
                ' , max(case when city = '''||x.city||''' then value else null end) as '||x.city;

                dbms_output.put_line(sql_query);
        end loop;

        sql_query := sql_query || ' from yourtable group by param order by param';
        dbms_output.put_line(sql_query);

        open p_cursor for sql_query;
    end;
/

Then to get your result, you can use the following (note: I used this in TOAD):

variable x refcursor
exec dynamic_pivot(:x)
print x

And the result of your query will be:

| PARAM | CITY1 | CITY2 |
-------------------------
|     a | value | value |
|     b | value | value |
|     c | value | value |