CODEWITHSUNDEEP
sqloracle-databasepivot
kinkajou
kinkajou

Reputation: 3728

Transformation of row and column of a database table

I have database table. As I further saw that the data was in a particular format. How to perform the following transformation without creating temporary table directly using query?col2 is not static field X and Y it is dynamically determined i.e no of columns is dynamic. enter image description here

Upvotes: 2

Views: 210

Answers (2)

Taryn
Taryn

Reputation: 247720

You did not specify which version of Oracle you are using so here are a few solutions.

If you are using Oracle 11g+, then you have access to the PIVOT function:

select *
from
(
  select col1, col2, col3
  from yourtable
) src
pivot
(
  max(col3)
  for col2 in ('X', 'Y')
) piv

See SQL Fiddle with Demo

If you are using another version of Oracle, then you can use an aggregate function with a CASE statement:

select col1,
  min(case when col2 = 'X' then col3 end) X,
  min(case when col2 = 'Y' then col3 end) Y
from yourtable
group by col1

See SQL Fiddle with Demo

If you have an unknown number of col2 values, then you can create a procedure in oracle to generate dynamic sql:

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

    begin
        for x in (select distinct col2 from yourtable order by 1)
        loop
            sql_query := sql_query ||
                ' , min(case when col2 = '''||x.col2||''' then col3 end) as '||x.col2;

                dbms_output.put_line(sql_query);
        end loop;

        sql_query := sql_query || ' from yourtable group by col1';

        open p_cursor for sql_query;
    end;
/

Then to execute it:

variable x refcursor
exec dynamic_pivot(:x)
print x

And the result, should be the same:

| COL1 |  X  |  Y |
--------------------
|    A |   1 |   3 |
|    B |   2 |   4 |

Upvotes: 2

Ahmad
Ahmad

Reputation: 12737

select 
  col1 " ",
  sum(case when col2='X' then col3 else 0 end) x,
sum(case when col2='Y' then col3 else 0 end) y
from sample
group by col1

==

SQL Fiddle Demo for this problem

SQL Fiddle Screenshot

Upvotes: 1

Related Questions