How to conver column to row and convert row to column?

Convert columns to rows:
create table tr1 (name varchar2(10), cust_value_1 number,cust_value_2 number,cust_value_3 number);
insert into tr1 values(‘a’,1,1,1);
insert into tr1 values(‘b’,1,0,1);
insert into tr1 values(‘c’,0,0,1);

–transform sql
select * from (
select      name,
case when rowno=1 then cust_value_1
when rowno=2 then cust_value_2
when rowno=3 then cust_value_3
end cust_value
from tr1, (select rownum rowno  from dual connect by level <= 3)
)
order by name;

NAME       CUST_VALUE
———- ———-
a                   1
a                   1
a                   1
b                   1
b                   0
b                   1
c                   0
c                   0
c                   1

9 rows selected.


Convert rows to columns:

–1.number or varchar
–create table and insert data
create table tr2 (name varchar2(10), cust_value number);
insert into tr2
select * from (
select      name,
case when rowno=1 then cust_value_1
when rowno=2 then cust_value_2
when rowno=3 then cust_value_3
end cust_value
from tr1, (select rownum rowno  from dual connect by level <= 3)
)
order by name;
SQL> select * from tr2;

NAME       CUST_VALUE
———- ———-
a                   1
a                   1
a                   1
b                   1
b                   0
b                   1
c                   0
c                   0
c                   1
9 rows selected.

–transform sql
SELECT name,
MAX(case when seq=1 then cust_value end) AS “cust_value_1”,
MAX(case when seq=2 then cust_value end) AS “cust_value_2”,
MAX(case when seq=3 then cust_value end) AS “cust_value_3”
FROM (SELECT name, cust_value, ROW_NUMBER() over(partition by name ORDER BY name) AS seq FROM tr2)
GROUP BY name;

NAME       cust_value_1 cust_value_2 cust_value_3
———- ———— ———— ————
a                     1            1            1
b                     1            0            1
c                     0            0            1

3 rows selected.

–2.connect varchar
create table tr2v(gid number,name varchar2(10));
insert into tr2v values (1,’Alex’);
insert into tr2v values (1,’Alice’);
insert into tr2v values (1,’Angle’);
insert into tr2v values (2,’Bob’);
insert into tr2v values (2,’Bill’);
insert into tr2v values (3,’Charlie’);
SQL> select * from tr2v;

GID NAME
———- ———-
1 Alex
1 Alice
1 Angle
2 Bob
2 Bill
3 Charlie

6 rows selected.

–transform sql
select gid,substr(SYS_CONNECT_BY_PATH(name,’,’),2) group_all
from (select gid, name,
count(*) over (partition by gid) cnt,
row_number() over (partition by gid order by name) seq
from tr2v
) where cnt=seq
start with seq=1 connect by prior seq+1=seq and prior gid=gid;

GID GROUP_ALL
———- —————–
1 Alex,Alice,Angle
2 Bill,Bob
3 Charlie

3 rows selected.

Advertisements

About Alex Zeng
I would be very happy if this blog can help you. I appreciate every honest comments. Please forgive me if I'm too busy to reply your comments in time.

6 Responses to How to conver column to row and convert row to column?

  1. alexzeng says:

    You can reference this link as well:
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:419593546543#72255255761453
    — Rows to columns:
    SELECT “1”, “2”, “3”, “4”, “5”, “6”
    FROM (SELECT MAX(decode(nr, 1, val, NULL)) AS “1”,
    MAX(decode(nr, 2, val, NULL)) AS “2”,
    MAX(decode(nr, 3, val, NULL)) AS “3”,
    MAX(decode(nr, 4, val, NULL)) AS “4”,
    MAX(decode(nr, 5, val, NULL)) AS “5”,
    MAX(decode(nr, 6, val, NULL)) AS “6”
    FROM (SELECT val, ROW_NUMBER() over(ORDER BY ord) AS nr
    FROM (/***** Your query goes here: *****/
    SELECT username AS val, username AS ord
    FROM all_users
    /*********************************/)))
    GROUP BY “1”, “2”, “3”, “4”, “5”, “6”;

    — Columns to rows:
    select decode(col, 1,”1″, 2,”2″, 3,”3″, 4,”4″) AS “Table”
    from (/***** Your query goes here: *****/
    SELECT owner AS “1”, table_name AS “2”, status AS “3”,
    tablespace_name AS “4”
    FROM ALL_TABLES
    WHERE ROWNUM = 1
    /*********************************/),
    (select ROWNUM AS col from all_objects WHERE ROWNUM <= 4)

  2. neworacledba says:

    this is an informative tip

  3. Vetrivel says:

    Hi,
    I have a below table step 1 and I would like to get output looks like step 2. Pls help me on this?

    Table Name: Rat_table
    —————————–

    year rating_name
    —— ——————-
    2011 ‘Sucessful’

    2010 ‘Not Rated’

    I want output like below format?

    Table Name: Rat_table
    ——————————————

    rating_name ‘Sucessful’ ‘Not Rated’
    year 2011 2010

    Thanks and Regards,

    Vetrivel-Saru

    • Alex Zeng says:

      I think you can convert column “year” and “rating_name” to row first, then attach them to one column. You may get too long column if there are many rows.
      -Alex

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: