Re: Transpose Oracle Table (Rows to columns)

From: Andrew Protasov <oracle_at_protasov.kiev.ua>
Date: Thu, 3 Dec 98 10:54:02 +0200
Message-ID: <AGg6bPsK42@protasov.kiev.ua>

Hi,

You can use decode approach:

SVRMGR> drop table test_cross;
Statement processed.

SVRMGR> create table test_cross
     2> (a varchar2(10)
     3> ,b varchar2(10)
     4> ,c varchar2(10)
     5> ,constraint test_cross_pk primary key (a,b)
     6> );

Statement processed.
SVRMGR> insert into test_cross(a,b,c) values('a1','b1','c1'); 1 row processed.
SVRMGR> insert into test_cross(a,b,c) values('a1','b2','c2'); 1 row processed.
SVRMGR> insert into test_cross(a,b,c) values('a1','b3','c3'); 1 row processed.
SVRMGR> insert into test_cross(a,b,c) values('a2','b1','c4'); 1 row processed.
SVRMGR> insert into test_cross(a,b,c) values('a2','b2','c5'); 1 row processed.
SVRMGR> insert into test_cross(a,b,c) values('a2','b3','c6'); 1 row processed.
SVRMGR> insert into test_cross(a,b,c) values('a3','b1','c7'); 1 row processed.
SVRMGR> insert into test_cross(a,b,c) values('a3','b2','c8'); 1 row processed.
SVRMGR> insert into test_cross(a,b,c) values('a3','b3','c9'); 1 row processed.
SVRMGR> commit;
Statement processed.

SVRMGR> select a,
     2>        max(decode(b,'b1',c,null)) b1,
     3>        max(decode(b,'b2',c,null)) b2,
     4>        max(decode(b,'b3',c,null)) b3
     5> from test_cross
     6> group by a;
A          B1         B2         B3
---------- ---------- ---------- ----------
a1         c1         c2         c3
a2         c4         c5         c6
a3         c7         c8         c9

3 rows selected.

But the query depends on values b1,b2,b3. You should determine this values at run time, create select statement as string and use dynamic sql (DBMS_SQL package) to execute.

Andrew Protasov

> Hi everybody,
>
> Could u please help me in solving a problem.
>
> The problem is to transpose columns to rows dynamically.
>
> The original table is -
>
> Proj Prot Pid Labcode Result
> 001 1 1 HGB 100.00
> 001 1 1 RBC 200.00
> 001 1 1 HCT 300.00
> 001 1 2 HGB 150.00
> 001 1 2 HCT 112.00
> 001 1 2 RBC 150.00
> 001 1 2 XYZ 102.00
>
> So, there could be say 60 labcodes vertically for each pid.
>
> Now, the above has to be transposed to -
>
> Proj Prot Pid HGB RBC HCT XYZ
> 001 1 1 100.00 200.00 300.00
> 001 1 2 150.00 150.00 112.00 102.00
>
> Any help towards a solution is highly appreciated.
>
> Thanks.
>
> Abhijit
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>
>
Received on Thu Dec 03 1998 - 02:54:02 CST