Transpose columns to rows dynamically

Hi everybody,

I would like to thank all of u who had sent me some solutions for my earlier query on the above subject. So, encouraged, I would like to present the problem in much more details and would appreciate if u could send me a general purpose solution. Actually the problem is much more complicated. The user is allowed to choose the columns she/he wishes to pick thru a front end (Forms 4.5) or in SQL+. Right now I want to try it through SQL+. So, say the use user picks up proj, prot, labcode(s), labresult(s), maxvalue(s), minvalue(s), labdate(s). So u see the columns chosen could be multiple and as such the rows become much more. This results in a lot more columns per proj, prot, labcode, labdate. We are using Oracle 8.3, so I believe there could be a maximum of 1000 columns. In order to illustrate the problem I have taken the example explicitly.

So table A contains many columns some of which are -

   Proj  Prot  Pid   Labcode  Labdt      Rslt    Maxv      Minv
   001    1    1     001      10-Jan-83  100.00  1000.00   100.00
   001    1    1     002      10-Jan-83  200.00  1000.00   200.00
   001    1    1     003      10-Jan-83  300.00  2000.00   300.00
   001    1    1     002      12-Jan-83  150.00  1000.00   100.00
   001    1    1     004      12-Jan-83  250.00  1000.00   200.00
   001    1    2     001      10-Jan-83  150.00  1000.00   100.00
   001    1    2     002      10-Jan-83  112.00  1000.00   200.00
   001    1    2     003      10_Jan-83  150.00  2000.00   300.00
   001    1    2     004      10-Jan-83  102.00  1000.00   200.00

Table B contains Labcode and desciption

Labcode Description

001       RBC
002       WBC
003       HRD
004       HGR
005       RTY


...       ...


...       ...

So table B might contain about say 50 labcodes and their description. This table is needed in the transposed table to get the description for the labcodes chosen by the user

So once the user selects the query like for proj=001, prot=1, pid=all, labcode=all, labdt between 10-Jan-83 and 15-Jan-83 give me rslt and maxv. So the transposed table (say Table C) would look something like this:-

Proj Prot Pid  Labdt      RBC(Rslt) RBC(Maxv)  WBC(Rslt)  WBC(Maxv) ...
001    1    1  10-Jan-83  100.00    1000.00    200.00     1000.00
001    1    1  12-Jan-83                       150.00     1000.00
001    1    2  10-Jan-83   150.00   1000.00    112.00     1000.00

So as u see if the user wants to query for 2 columns(Rslt and Maxv for each labcode), each row spans across by a multiplication factor of 2. That means if there are 10 labcodes, there would be 10*2 (20) columns horizontally, in addition to the columns like Proj, Prot, Pid, Labdt.

Now the result of this query will be input for SAS dataset, where lot of statistical analysis is made.

I hope I have been able to put the whole problem expicitly.

Thanks a ton!

Abhijit.

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Sat Dec 05 1998 - 11:15:09 CST