Urgent : Transpose columns to rows dynamically

Hi there,
Any help towards solving the interesting problem.

Thanks!
Abhijit

In article <74bpdn$jtu$1_at_nnrp1.dejanews.com>,   abhijitde_at_hotmail.com wrote:
> 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
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Sun Dec 06 1998 - 16:40:14 CST