1

This is the ddl for the tables that I'm using.

CREATE TABLE "BISAMPLE"."SUMLEDGER" 
   (    "FUND" VARCHAR2(6 BYTE), 
    "ORG" VARCHAR2(6 BYTE), 
    "ACCT" VARCHAR2(6 BYTE), 
    "PROG" VARCHAR2(6 BYTE), 
    "YEAR" NUMBER, 
    "S01_BUDGET" NUMBER, 
    "S01_BUDGET_ADJ" NUMBER, 
    "S01_YTD_ACTV" NUMBER, 
    "S01_ENCUMB" NUMBER, 
    "S02_BUDGET" NUMBER, 
    "S02_BUDGET_ADJ" NUMBER, 
    "S02_YTD_ACTV" NUMBER, 
    "S02_ENCUMB" NUMBER
   )

and here is the insert of the sample data.

REM INSERTING into SUMLEDGER
SET DEFINE OFF;
Insert into SUMLEDGER (FUND,ORG,ACCT,PROG,YEAR,S01_BUDGET,S01_BUDGET_ADJ,S01_YTD_ACTV,S01_ENCUMB,S02_BUDGET,S02_BUDGET_ADJ,S02_YTD_ACTV,S02_ENCUMB) values ('100001','3999','3220','06',6,20,30,15,15,10,20,10,40);
Insert into SUMLEDGER (FUND,ORG,ACCT,PROG,YEAR,S01_BUDGET,S01_BUDGET_ADJ,S01_YTD_ACTV,S01_ENCUMB,S02_BUDGET,S02_BUDGET_ADJ,S02_YTD_ACTV,S02_ENCUMB) values ('100001','3999','3220','06',7,20,30,15,15,10,20,30,40);
Insert into SUMLEDGER (FUND,ORG,ACCT,PROG,YEAR,S01_BUDGET,S01_BUDGET_ADJ,S01_YTD_ACTV,S01_ENCUMB,S02_BUDGET,S02_BUDGET_ADJ,S02_YTD_ACTV,S02_ENCUMB) values ('100001','3999','3460','06',6,25,30,20,20,20,20,30,40);
Insert into SUMLEDGER (FUND,ORG,ACCT,PROG,YEAR,S01_BUDGET,S01_BUDGET_ADJ,S01_YTD_ACTV,S01_ENCUMB,S02_BUDGET,S02_BUDGET_ADJ,S02_YTD_ACTV,S02_ENCUMB) values ('100001','3999','3220','06',8,20,35,25,30,30,22,15,30);

This is the second table that will be using in this query.

CREATE TABLE "BISAMPLE"."FUNDING" 
   (    "FUNDING_START_DATE" DATE, 
    "FUNDING_NUMBER" VARCHAR2(6 BYTE)
   )

and here is the table insert.

REM INSERTING into FUNDING
SET DEFINE OFF;
Insert into FUNDING (FUNDING_START_DATE,FUNDING_NUMBER) values (to_date('21-JAN-2017 03:47:42','DD-MON-RRRR HH24:MI:SS'),'100001');

I'm trying to use the unpivot function to convert my columns to rows. But I'm quite unsuccessful at the moment.However, I can use the following query to acomplish the same output.

SELECT 
 FUND,
  ORG,
  ACCT,
  PROG,
  YEAR,
  SUM(S01_BUDGET + S01_BUDGET_ADJ) BUDG,
   S01_YTD_ACTV YEAR_TO_DATE,
   S01_ENCUMB ENCUMBRANCE,
  '01' AS SPERIOD,
  CASE
        --PERIOD 01
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'JAN'
      THEN '01' --JAN PERIOD 01
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'FEB'
      THEN '02' --FEB
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'MAR'
      THEN '03' --MAR
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'APR'
      THEN '04'
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'MAY'
      THEN '05'
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'JUN'
      THEN '06'
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'JUL'
      THEN '07'
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'AUG'
      THEN '08' --AUG
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'SEP'
      THEN '09'
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'OCT'
      THEN '10' --OCT
      WHEN SUBSTR(TO_CHAR(FUNDING_NUMBER,'MON-YYYY'),1,3) = 'NOV'
      THEN '11'
      WHEN SUBSTR(TO_CHAR(FUNDING_NUMBER,'MON-YYYY'),1,3) = 'DEC'
      THEN '12'
      ELSE '00'
    END AS SMONTH,

  (CASE
        --PERIOD 01
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'JAN'
      THEN TO_NUMBER(YEAR)
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'FEB'
      THEN TO_NUMBER(YEAR)
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'MAR'
      THEN TO_NUMBER(YEAR)
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'APR'
      THEN TO_NUMBER(YEAR)
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'MAY'
      THEN TO_NUMBER(YEAR)
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'JUN'
      THEN TO_NUMBER(YEAR)
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'JUL'
      THEN TO_NUMBER(YEAR)
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'AUG'
      THEN TO_NUMBER(YEAR)
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'SEP'
      THEN TO_NUMBER(YEAR)
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'OCT'
      THEN TO_NUMBER(YEAR)
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'NOV'
      THEN TO_NUMBER(YEAR)
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'DEC'
      THEN TO_NUMBER(YEAR)
      ELSE TO_NUMBER(YEAR)
    END) AS S_YEAR
FROM  SUMLEDGER ,
      FUNDING
WHERE FUNDING_NUMBER =  FUND
group by 
FUND,
  ORG,
  ACCT,
  PROG,
  YEAR,
   S01_YTD_ACTV ,
   S01_ENCUMB,
  '01' ,
  CASE
        --PERIOD 01
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'JAN'
      THEN '01' --JAN PERIOD 01
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'FEB'
      THEN '02' --FEB
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'MAR'
      THEN '03' --MAR
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'APR'
      THEN '04'
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'MAY'
      THEN '05'
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'JUN'
      THEN '06'
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'JUL'
      THEN '07'
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'AUG'
      THEN '08' --AUG
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'SEP'
      THEN '09'
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'OCT'
      THEN '10' --OCT
      WHEN SUBSTR(TO_CHAR(FUNDING_NUMBER,'MON-YYYY'),1,3) = 'NOV'
      THEN '11'
      WHEN SUBSTR(TO_CHAR(FUNDING_NUMBER,'MON-YYYY'),1,3) = 'DEC'
      THEN '12'
      ELSE '00'
    END ,

  (CASE
        --PERIOD 01
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'JAN'
      THEN TO_NUMBER(YEAR)
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'FEB'
      THEN TO_NUMBER(YEAR)
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'MAR'
      THEN TO_NUMBER(YEAR)
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'APR'
      THEN TO_NUMBER(YEAR)
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'MAY'
      THEN TO_NUMBER(YEAR)
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'JUN'
      THEN TO_NUMBER(YEAR)
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'JUL'
      THEN TO_NUMBER(YEAR)
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'AUG'
      THEN TO_NUMBER(YEAR)
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'SEP'
      THEN TO_NUMBER(YEAR)
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'OCT'
      THEN TO_NUMBER(YEAR)
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'NOV'
      THEN TO_NUMBER(YEAR)
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'DEC'
      THEN TO_NUMBER(YEAR)
      ELSE TO_NUMBER(YEAR)
    END) 


UNION ALL

SELECT 
  FUND,
  ORG,
  ACCT,
  PROG,
  YEAR,
  SUM(S02_BUDGET + S02_BUDGET_ADJ) BUDG,
   S02_YTD_ACTV YEAR_TO_DATE,
   S02_ENCUMB ENCUMBRANCE,
  '01' AS SPERIOD,
  CASE
        --PERIOD 01
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'JAN'
      THEN '02' --JAN PERIOD 01
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'FEB'
      THEN '03' --FEB
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'MAR'
      THEN '04' --MAR
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'APR'
      THEN '05'
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'MAY'
      THEN '06'
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'JUN'
      THEN '07'
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'JUL'
      THEN '08'
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'AUG'
      THEN '09' --AUG
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'SEP'
      THEN '10'
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'OCT'
      THEN '11' --OCT
      WHEN SUBSTR(TO_CHAR(FUNDING_NUMBER,'MON-YYYY'),1,3) = 'NOV'
      THEN '12'
      WHEN SUBSTR(TO_CHAR(FUNDING_NUMBER,'MON-YYYY'),1,3) = 'DEC'
      THEN '01'
      ELSE '00'
    END AS SMONTH,

  (CASE
        --PERIOD 02
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'JAN'
      THEN TO_NUMBER(YEAR)
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'FEB'
      THEN TO_NUMBER(YEAR)
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'MAR'
      THEN TO_NUMBER(YEAR)
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'APR'
      THEN TO_NUMBER(YEAR)
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'MAY'
      THEN TO_NUMBER(YEAR)
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'JUN'
      THEN TO_NUMBER(YEAR)
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'JUL'
      THEN TO_NUMBER(YEAR)
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'AUG'
      THEN TO_NUMBER(YEAR)
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'SEP'
      THEN TO_NUMBER(YEAR)
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'OCT'
      THEN TO_NUMBER(YEAR)
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'NOV'
      THEN TO_NUMBER(YEAR)
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'DEC'
      THEN TO_NUMBER(YEAR)+1
      ELSE TO_NUMBER(YEAR)
    END) AS S_YEAR
FROM SUMLEDGER , 
     FUNDING
WHERE FUNDING_NUMBER =  FUND
group by 
FUND,
  ORG,
  ACCT,
  PROG,
  YEAR,
   S02_YTD_ACTV ,
   S02_ENCUMB ,
  '01' ,
  CASE
        --PERIOD 01
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'JAN'
      THEN '02' --JAN PERIOD 01
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'FEB'
      THEN '03' --FEB
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'MAR'
      THEN '04' --MAR
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'APR'
      THEN '05'
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'MAY'
      THEN '06'
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'JUN'
      THEN '07'
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'JUL'
      THEN '08'
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'AUG'
      THEN '09' --AUG
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'SEP'
      THEN '10'
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'OCT'
      THEN '11' --OCT
      WHEN SUBSTR(TO_CHAR(FUNDING_NUMBER,'MON-YYYY'),1,3) = 'NOV'
      THEN '12'
      WHEN SUBSTR(TO_CHAR(FUNDING_NUMBER,'MON-YYYY'),1,3) = 'DEC'
      THEN '01'
      ELSE '00'
    END ,

  (CASE
        --PERIOD 02
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'JAN'
      THEN TO_NUMBER(YEAR)
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'FEB'
      THEN TO_NUMBER(YEAR)
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'MAR'
      THEN TO_NUMBER(YEAR)
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'APR'
      THEN TO_NUMBER(YEAR)
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'MAY'
      THEN TO_NUMBER(YEAR)
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'JUN'
      THEN TO_NUMBER(YEAR)
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'JUL'
      THEN TO_NUMBER(YEAR)
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'AUG'
      THEN TO_NUMBER(YEAR)
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'SEP'
      THEN TO_NUMBER(YEAR)
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'OCT'
      THEN TO_NUMBER(YEAR)
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'NOV'
      THEN TO_NUMBER(YEAR)
      WHEN SUBSTR(TO_CHAR(FUNDING_START_DATE,'MON-YYYY'),1,3) = 'DEC'
      THEN TO_NUMBER(YEAR)+1
      ELSE TO_NUMBER(YEAR)
    END)

Can someone help me re-write this using the unpivot function? Using the above approach would require me to union at least 14 times because there is 14 possible periods/columns One quick heads up this table contain roughly 98 columns I have reduced the example here to a handful of columns to better explain and not overwhelm.

Here is the output I'm looking to replicate

"FUND"  "ORG"   "ACCT"  "PROG"  "YEAR"  "BUDG"  "YEAR_TO_DATE"  "ENCUMBRANCE"   "SPERIOD"   "SMONTH"    "S_YEAR"
"100001"    "3999"  "3220"  "06"    6   50  15  15  "01"    "01"    6
"100001"    "3999"  "3220"  "06"    7   50  15  15  "01"    "01"    7
"100001"    "3999"  "3220"  "06"    8   55  25  30  "01"    "01"    8
"100001"    "3999"  "3460"  "06"    6   55  20  20  "01"    "01"    6
"100001"    "3999"  "3220"  "06"    6   30  10  40  "01"    "02"    6
"100001"    "3999"  "3220"  "06"    8   52  15  30  "01"    "02"    8
"100001"    "3999"  "3460"  "06"    6   40  30  40  "01"    "02"    6
"100001"    "3999"  "3220"  "06"    7   30  30  40  "01"    "02"    7
Improve this question
1
  • Still trying to understand what is going on here (I will add an Answer if I can understand the question and I can answer it). A quick observation though, substr(to_char(some_date, 'MON-YYYY'), 1, 3) can be written much more shortly and efficiently as to_char(some_date, 'MON'); and the CASE statements can be written more compactly like so: CASE TO_CHAR(some_date, 'MON') WHEN 'JAN' THEN ... WHEN 'FEB' THEN ... - this way you don't need to repeat the expression 12 times. Commented Jan 21, 2017 at 7:16

1 Answer 1

Reset to default
1

Your speriod in the expected result is incorrect. You should have 02 for last four rows.

Try this:

select
  s.fund,
  s.org,
  s.acct,
  s.prog,
  s.year,
  s.budget + s.budget_adj budg,
  s.year_to_date,
  s.encumbrance,
  s.s_period,
  to_number(to_char(add_months(f.funding_start_date, s_period - 1), 'MM')) s_month,
  decode(to_char(f.funding_start_date,'MM'),'12',year + s_period - 1, year) s_year
from (
  select *
  from sumledger
  unpivot ( 
    (budget, budget_adj, year_to_date, encumbrance) 
    for s_period in (
      (s01_budget,s01_budget_adj,s01_ytd_actv,s01_encumb) as 1,
      (s02_budget,s02_budget_adj,s02_ytd_actv,s02_encumb) as 2)
  )
) s inner join funding f on s.fund = f.funding_number;

Produces:

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

You are are awesome Gurv. Thank you!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.