Home » SQL & PL/SQL » SQL & PL/SQL » Pivoting or Transposing (Oracle 9i)
Pivoting or Transposing [message #423518] Fri, 25 September 2009 00:33 Go to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi,
I have to display a table record in a pivot format.

To illustrate the requirement,

GROUP_KEY  PIVOT_KEY  VAL
---------- ---------- ----------
G0         P0         a
G0         P1         A
G0         P2         1

G1         P0         b
G1         P1         B
G1         P2         v

G2         P0         c
G2         P1         3
G2         P2         2

I need to rearrange them into a matrix like this. 

GROUP_KEY  P0         P1         P2         
---------- ---------- ---------- ---------- 
G0         a          A          1          
G1         b          B          v
G2         c          3          2
 


I have the table create statement as :-

CREATE TABLE Mineral_det
(
LOT_no VARCHAR2(254 BYTE),
Mineral VARCHAR2(40 BYTE),
Rate VARCHAR2(254 BYTE)
)

And some insert statements :-

INSERT INTO MINERAL_DET ( LOT_NO, MINERAL, RATE ) VALUES (
'N0753246', 'Avg of Calcium', '1.38');
INSERT INTO MINERAL_DET ( LOT_NO, MINERAL, RATE ) VALUES (
'N0753246', 'Avg of Phosphorus', '1.06');
INSERT INTO MINERAL_DET ( LOT_NO, MINERAL, RATE ) VALUES (
'N0753246', 'Avg of Magnesium', '0.11');
INSERT INTO MINERAL_DET ( LOT_NO, MINERAL, RATE ) VALUES (
'N0753246', 'Avg of Potassium', '1.73');
INSERT INTO MINERAL_DET ( LOT_NO, MINERAL, RATE ) VALUES (
'N0753246', 'Avg of Sodium', '0.37');
INSERT INTO MINERAL_DET ( LOT_NO, MINERAL, RATE ) VALUES (
'N0753246', 'Avg of Protein', '37.3');
INSERT INTO MINERAL_DET ( LOT_NO, MINERAL, RATE ) VALUES (
'N0753246', 'RM Chloride result', '0.98');
INSERT INTO MINERAL_DET ( LOT_NO, MINERAL, RATE ) VALUES (
'N0753231', 'Avg of Calcium', '0.86');
INSERT INTO MINERAL_DET ( LOT_NO, MINERAL, RATE ) VALUES (
'N0753231', 'Avg of Phosphorus', '0.67');
INSERT INTO MINERAL_DET ( LOT_NO, MINERAL, RATE ) VALUES (
'N0753231', 'Avg of Sodium', '0.56');
INSERT INTO MINERAL_DET ( LOT_NO, MINERAL, RATE ) VALUES (
'N0753231', 'Avg of Potassium', '2.19');
INSERT INTO MINERAL_DET ( LOT_NO, MINERAL, RATE ) VALUES (
'N0753231', 'Avg of Magnesium', '0.11');
INSERT INTO MINERAL_DET ( LOT_NO, MINERAL, RATE ) VALUES (
'N0753231', 'Avg of Protein', '35.8');
INSERT INTO MINERAL_DET ( LOT_NO, MINERAL, RATE ) VALUES (
'N0753231', 'RM Chloride result', '1.60');

The first part is the illustration of the display I need and the actual table and data are gven below it.

The "Group_key" is similar as 'LOT_NO',
"PIVOT_KEY" is similar as 'Mineral' and
"Val" is similar as 'Rate'.

So I need to display the data from table Mineral_det in a pivot form where first row should be having headers as :-
'Lot NO', 'Minerals'............ which would display as :-
'Lot NO', 'Avg of Calcium', 'Avg of Phosphorus', 'Avg of Magnesium', ...... which should be distinct...

Please give me some hints as I don't have to hard code the Minerals but they should come dynamically as columns because minerals can be added to the table.

Regards,
Mona
Re: Pivoting or Transposing [message #423520 is a reply to message #423518] Fri, 25 September 2009 00:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And what happen if you have 1000 mineraals, do you have 1000 columns? How do you display and read them?

Regards
Michel
Re: Pivoting or Transposing [message #423523 is a reply to message #423520] Fri, 25 September 2009 01:02 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi Michel,
Thanks for looking into this.... there are 46 minerals as of now. It might increase to 10 more in future ..not sure...!

I have to display it in a grid with a scrollbar.

Regards,
Mona
Re: Pivoting or Transposing [message #423524 is a reply to message #423523] Fri, 25 September 2009 01:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This can't be done in SQL.
You have to take advantage of your client program features.

Regards
Michel
Re: Pivoting or Transposing [message #423525 is a reply to message #423524] Fri, 25 September 2009 01:12 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

I didn't get you. I have to finally display it in a grid in dot net.

Do you mean that I have to hard code the Minerals and get the result( as I searched on google using DECODE).

Is there no way I can display the output for dynamic columns?
Please help me on this as I have to do this for the client.
Re: Pivoting or Transposing [message #423526 is a reply to message #423525] Fri, 25 September 2009 01:33 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not in SQL.
You have to do it in your .Net program.

Regards
Michel
Previous Topic: how to get the user who locked a record
Next Topic: Selecting a random record from table
Goto Forum:
  


Current Time: Fri Dec 02 14:31:56 CST 2016

Total time taken to generate the page: 0.20136 seconds