Sunday, November 11, 2012

Oracle SQL:Converting Row to Columns

Rows to columns..This is second time i came across this requirement of converting rows as columns.


I remember this question in one of the exercises in oracle OCP certification program.The solution was to use Decode and max functionality in combination

Posting it here for easy reference and for people new to oracle.

In the below query our requirement was to get the current quarter and next quarter from a custom calendar in one row..use a similar approach whenever u have similar requirement


SELECT MAX (DECODE (ROWNUM, 1, display_forecast, NULL))
Period1_status_frcst,
MAX (DECODE (ROWNUM, 2, display_forecast, NULL))
Period2_status_frcst,
MAX (DECODE (ROWNUM, 3, display_forecast, NULL))
Period3_status_frcst,
MAX (DECODE (ROWNUM, 1, period_name, NULL)) Period1_disp,
MAX (DECODE (ROWNUM, 1, start_date, NULL)) Period1_start_dt,
MAX (DECODE (ROWNUM, 1, quarter_num, NULL)) Current_quarter,
MAX (DECODE (ROWNUM, 1, end_date, NULL)) Period1_end_dt,
MAX (DECODE (ROWNUM, 2, period_name, NULL)) Period2_disp,
MAX (DECODE (ROWNUM, 2, start_date, NULL)) Period2_start_dt,
MAX (DECODE (ROWNUM, 2, end_date, NULL)) Period2_end_dt,
MAX (DECODE (ROWNUM, 3, period_name, NULL)) Period3_disp,
MAX (DECODE (ROWNUM, 3, start_date, NULL)) Period3_start_dt,
MAX (DECODE (ROWNUM, 3, end_date, NULL)) Period3_end_dt,
MAX (DECODE (ROWNUM, 1, period_name, NULL)) Period1_disp_actual,
MAX (DECODE (ROWNUM, 2, period_name, NULL)) Period2_disp_actual,
MAX (DECODE (ROWNUM, 3, period_name, NULL)) Period3_disp_actual,
MAX (DECODE (ROWNUM, 4, period_name, NULL)) Period4_disp,
MAX (DECODE (ROWNUM, 4, start_date, NULL)) Period4_start_dt,
MAX (DECODE (ROWNUM, 4, end_date, NULL)) Period4_end_dt,
MAX (DECODE (ROWNUM, 4, quarter_num, NULL)) next_quarter,
MAX (DECODE (ROWNUM, 5, period_name, NULL)) Period5_disp,
MAX (DECODE (ROWNUM, 5, start_date, NULL)) Period5_start_dt,
MAX (DECODE (ROWNUM, 5, end_date, NULL)) Period5_end_dt,
MAX (DECODE (ROWNUM, 6, period_name, NULL)) Period6_disp,
MAX (DECODE (ROWNUM, 6, start_date, NULL)) Period6_start_dt,
MAX (DECODE (ROWNUM, 6, end_date, NULL)) Period6_end_dt
FROM ( SELECT CASE
WHEN sysdate > end_date
THEN
'P'
WHEN sysdate BETWEEN start_Date
AND end_date
THEN
'C'
ELSE
'F'
END
display_forecast,
period_name,
start_date,
end_date,
quarter_num
FROM gl_periods
WHERE period_set_name = 'Fiscal Year'
AND ADJUSTMENT_PERIOD_FLAG = 'N'
AND (quarter_num, period_year) IN
(SELECT quarter_num, period_year
FROM gl_periods
WHERE sysdate BETWEEN start_date
AND end_Date
AND period_set_name = 'Fiscal Year'
AND ADJUSTMENT_PERIOD_FLAG = 'N')
OR (quarter_num, period_year) IN
(SELECT DECODE (quarter_num, 4, 1, quarter_num + 1),
DECODE (quarter_num,
4, period_year + 1,
period_year)
FROM gl_periods
WHERE sysdate BETWEEN start_date
AND end_Date
AND period_set_name = 'Fiscal Year'
AND ADJUSTMENT_PERIOD_FLAG = 'N')
)
ORDER BY start_date ASC



5 comments:

Sridevi Koduru said...
This comment has been removed by the author.
Cub Training said...

Its impressive to know something about your note on Oracle apps Course. Please do share your articles like this your articles for our awareness. Mostly we do also provide Online Training on Cub training oracle apps course.

Sridevi Koduru said...
This comment has been removed by the author.
Sridevi Koduru said...


Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.

oracle fusion said...

CALFRE is the local search engine where you can find various training institutes that provide
training for Oracle Fusion HCM through online in Hyderabad.

Oracle Fusion Financial Training Institute