How to conver columns to rows and convert rows to columns?

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.