Home > Default > 11G SQL pivot example?

11G SQL pivot example?

November 30Hits:0
Advertisement
Hello,
Can anyone help me write a SQL pivot statement using 11G to do the following?:
Table columns
=========
Deliverable
Phase (For simplicity we'll make the total possible Phase values equal 1 to 13)
Delv_IN_Phase_Y_N Char(3) values 'Yes' or 'No'
I want to make a matrix with these 3 columns in the above table (in reality a complex view) :
- Deliverable is first column.
- Next 13 column headers display 1 to 13 (the posiible values contained in the 'Phase' column).
- The matrix values under the 'Phase' Column headers are the Yes/No values held in the Delv_in_Phase column.
Deliverable Phase 1 Phase 2 Phase 3 Phase 4 ......... Phase 13
=========================================================
Product Market Plan Yes No No Yes No
Bid Plan No Yes No ...........................................
Contract Summary ................................................................................
Quality Plan .................................................................................
Thanks for any help in advance.
Carol

Answers

Just a simple example based on what I could grasp from your table description.
I assume you can't have more than 1 value (either 'yes' or 'no' for a given deliverable in each phase).
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
Connected as fsitja
SQL> with t as (
  2  select 'Product Market Plan' deliverable, 1 phase, 'NO' Delv_IN_Phase_Y_N from dual union all
  3  select 'Product Market Plan' deliverable, 2 phase, 'YES' Delv_IN_Phase_Y_N from dual union all
  4  select 'Product Market Plan' deliverable, 3 phase, 'YES' Delv_IN_Phase_Y_N from dual union all
  5  select 'Bid Plan', 1, 'YES' from dual union all
  6  select 'Bid Plan', 2, 'NO' from dual union all
  7  select 'Bid Plan', 3, 'NO' from dual union all
  8  select 'Contract Summary', 1, 'NO' from dual union all
  9  select 'Contract Summary', 2, 'NO' from dual union all
10  select 'Contract Summary', 3, 'YES' from dual union all
11  select 'Quality Plan', 1, 'YES' from dual union all
12  select 'Quality Plan', 2, 'YES' from dual union all
13  select 'Quality Plan', 3, 'NO' from dual)
14  -- END OF SAMPLE DATA
15  SELECT *
16    FROM t
17   PIVOT(MAX(delv_in_phase_y_n) FOR phase IN (1 AS phase_1, 2 AS phase_2, 3 AS phase_3))
18  /
DELIVERABLE         PHASE_1 PHASE_2 PHASE_3
Contract Summary    NO      NO      YES
Bid Plan            YES     NO      NO
Product Market Plan NO      YES     YES
Quality Plan        YES     YES     NO
SQL> You can play around and expand the pivot by adding the whole 13 values inside the "FOR phase IN (val1 as column1, etc)" just thought I'd keep it simple.
=> [Documentation Reference here|http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/statements_10002.htm#CHDCEJJE]
Regards.

Read other 9 answers

Tags:

Related Articles

  • Can we use multiple "pivot_for_clauses" in 11g SQL PIVOTOctober 11

    Can we use multiple "pivot_for_clauses" in 11g SQL PIVOT. Below SQL is an example of what I am trying to do - In this case instead of using JOIN, can I have three pivot_for_clauses in the same sql? SQL: MERGE INTO Test_1 dest USING (SELECT P1.co

  • 11G SQL pivot example?November 30

    Hello, Can anyone help me write a SQL pivot statement using 11G to do the following?: Table columns ========= Deliverable Phase (For simplicity we'll make the total possible Phase values equal 1 to 13) Delv_IN_Phase_Y_N Char(3) values 'Yes' or 'No' I

  • Oracle Database 11g: SQL Fundamentals I 1Z0-051 QuestionOctober 11

    i bought the OCA Oracle Database 11g: SQL Fundamentals I Exam Guide (Exam 1Z0-051) and i am not sure do i have to read the whole book ! or just go through the exam objectives table ? because the book covers lots of topics that's not in the exam and i

  • Book: oracle database 11g sql fundamentals iNovember 30

    dear all, i would like to pass the oracle certification in forms application. where can i find the ebook (pdf) of the fist course (oracle database 11g sql fundamentals i) in order to study and applying to take the Oracle PL/SQL Developer Certified As

  • May i go for sql expert(1Z0-047) exam or 11g sql fundamentals 1(1Z0-051)November 30

    Hi all, Please suggest me, may i go for sql expert(1Z0-047) Test or 11g sql fundamentals 1(1Z0-051) Test with oracle Administration 1(1Z0-042) Test. Which will be the best option. Thanks & RegardsYou may use either exam along with the Oracle Administ

  • 1z0-051 Oracle Database 11g SQL Fundamentals 1 exam DUMPSNovember 30

    Hi dear associates. can you please help me 1z0-051 Oracle Database 11g SQL Fundamentals 1 exam dumps for preparation sql fundamental exam......!https://blogs.oracle.com/certification/entry/0477 https://blogs.oracle.com/certification/entry/the_route_y

  • Do i have to pay exam center fee to an authorized oracle exam center(new horizon) in Bangladesh rather than the exam fee of Oracle Database 11g: SQL Fundamentals I ?November 30

    Do i have to pay exam center fee to an authorized oracle exam center(new horizon) in Bangladesh rather than the exam fee of Oracle Database 11g: SQL Fundamentals I ?I agree with Matthew that I cannot be sure exactly what you are asking. Generally i r

  • Dynamic SQL PIVOT not producing output?October 11

    Hey all, Find my source code with test data scripts below. Since my production system is not connected to the inet, I had to type this "by hand" as it were, so please pardon any mispellings. I have no way to test on my inet-enabled PC before pos

  • 11g SQL query syntax/results differ from 10gOctober 11

    Hello, A bit of an odd situation.  We have a report in 10g that is working as expected and when we run the same report in 11g we slightly different results.  The same physical tables and columns are being used between each report and the number of re

  • OBIEE 11g default pivot formatting changesNovember 30

    Hi, in OBIEE 11g, there are several default pivot table formatting options that we would like to disable - I don't want end users to have to do this manually: #1 - All cells that are not in the "Measures" area have a default tan background appli

Copyright (C) 2019 wisumpire.com, All Rights Reserved. webmaster#wisumpire.com 14 q. 1.360 s.