0

Can anybody help me pivot the data I get in my SQL*Plus query? I've tried to figure this out myself by looking at prior questions but have had little success.

sqlplus $USERNAME@ORACLE_SID/$PASSWORD<< EOQ> output.file
SET LINESIZE 200
SELECT sd.SW_NAME, sd.SAMPLE_NUM, shv.DATE_SAMPLE, sd.ANALYSIS_NAME, 
sd.ANALYSIS_RESULT, sd.BU_ID
FROM public_project.Sample_Data_Pbu_Lab_Vew sd, public_project.Sample_Header_Vew shv 
WHERE(sd.SW_NAME LIKE '02-40%') AND 
(sd.ANALYSIS_NAME IN('C6P-MOL%', 'CO2-MOL%', 'ETHANE-MOL%', 'I-BUTANE-MOL%')) 
AND sd.BU_ID='PBU' AND shv.DATE_SAMPLE>=to_date('2013-04-04','YYYY-MM-DD')
ORDER BY sd.SW_NAME, shv.DATE_SAMPLE, sd.SAMPLE_NUM;
EOQ

With this code I get all the data I want, but in this format:

SW_NAME  SAMPLE_NUM  DATE_SAMPL ANALYSIS_NAME     ANALYSIS_RESULT BU_ID
-------- ----------- ---------- ----------------- --------------- ------
02-40    PC43602     2013-04-04 C6P-MOL%          .208            PBU
02-40    PC43602     2013-04-04 CO2-MOL%          12.302          PBU
02-40    PC43602     2013-04-04 ETHANE-MOL%       5.333           PBU
02-40    PC43602     2013-04-04 I-BUTANE-MOL%     .154            PBU

What I'm trying to get is something like this:

SW_NAME  SAMPLE_NUM  DATE_SAMPL C6P-MOL%  CO2-MOL%  ETHANE-MOL%  I-BUTANE-MOL%  BU_ID
-------- ----------- ---------- --------- --------- ------------ -------------- ------
02-40    PC43602     2013-04-04 .208      12.302    5.333        .154           PBU

Just to clarify, I am not trying to sum the data at all. Thank you for any help in advance :)

Marko8119
  • 21
  • 2
  • What version of Oracle? I assume based on the query that the columns you want to pivot are known at compile time. – Justin Cave Jun 28 '13 at 01:09
  • Thanks @JustinCave for getting back to me. I do know the columns I want to pivot at compile time. The version is Oracle9i 9.2.0.8.0. I saw the earlier question but in that case a table is created first and the pivot involves a summation. Can I pivot the columns without first creating a table? – Marko8119 Jun 28 '13 at 01:48
  • What do you mean "without creating a table"? You seem to have two tables already in the query you posted. Are you trying to do something without either of those two tables? If so, where is the data coming from? – Justin Cave Jun 28 '13 at 02:20
  • Please clarify your requirements: do you always want the final output written to file? – APC Jun 28 '13 at 03:40
  • You say you don't know the column names at compile time. THis means you need to use dynamic SQL. So, do you have the rights to create stored procedures? Also, whats the interface? How do the column names end up in the query at the moment? – APC Jun 28 '13 at 03:47
  • Thanks for getting back to me. I'm usually a numerical C++/Fortran guy and this kind of coding is new to me. I apologize for being unclear. I mean without explicitly issuing a "create table" command. I do know the names of the columns at compile time. I'm just writing the above in a bash script. I don't think I have rights to create stored procedures. I was trying to use the pivot command but I now know that's only available in version 9i. – Marko8119 Jun 28 '13 at 05:07

0 Answers0