Need help converting rows to columns
- From: Sandra Becker <sbecker6925@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 22 Sep 2014 07:31:11 -0600
Oracle 11gR2
I need to set up an Apex report to show tablespace growth (or lack thereof)
for a rolling 7 day window. The data is pulled from a table where we store
the information for 2 years. I have a query which returns the required
information, but it displays one row per day. I want the data to display
in columns by date. Below is my query, the current output and the desired
output. I've tried various iterations, but the data doesn't come out in
date order.
The report lists only days where the pct_full >= 75. It isn't necessary to
display the dates, but the current query does so I can verify the order.
Nulls on any given day are acceptable. Any help is appreciated.
QUERY:
SELECT
host,
db_name,
tablespace,
TO_NUMBER(TO_CHAR(insert_dt,'yyyymmdd')) ins_dt,
CASE WHEN TO_CHAR(insert_dt,'yyyymmdd') = TO_CHAR(sysdate-6,'yyyymmdd')
THEN pct_full END day1,
CASE WHEN TO_CHAR(insert_dt,'yyyymmdd') = TO_CHAR(sysdate-5,'yyyymmdd')
THEN pct_full END day2,
CASE WHEN TO_CHAR(insert_dt,'yyyymmdd') = TO_CHAR(sysdate-4,'yyyymmdd')
THEN pct_full END day3,
CASE WHEN TO_CHAR(insert_dt,'yyyymmdd') = TO_CHAR(sysdate-3,'yyyymmdd')
THEN pct_full END day4,
CASE WHEN TO_CHAR(insert_dt,'yyyymmdd') = TO_CHAR(sysdate-2,'yyyymmdd')
THEN pct_full END day5,
CASE WHEN TO_CHAR(insert_dt,'yyyymmdd') = TO_CHAR(sysdate-1,'yyyymmdd')
THEN pct_full END day6,
CASE WHEN TO_CHAR(insert_dt,'yyyymmdd') = TO_CHAR(sysdate,'yyyymmdd')
THEN pct_full END day7
FROM ghx_apex_apps.ts_freespace
WHERE insert_dt >= trunc(sysdate-6)
AND pct_full >= 75
AND tablespace = 'ETL_DATA'
ORDER BY
db_name,
tablespace,
insert_dt;
CURRENT OUTPUT:
HOST DB_NAME TABLESPACE INS_DT DAY1 DAY2
DAY3 DAY4 DAY5 DAY6 DAY7
-------- ---------------- ------------------ ---------- ------- -------
------- ------- ------- ------- -------
PRDDB4 PEDW ETL_DATA 20140913 76.28 <Null>
<Null> <Null> <Null> <Null> <Null>
PRDDB4 20140914 <Null> 75.82
<Null> <Null> <Null> <Null> <Null>
PRDDB4 20140915 <Null> <Null>
75.21 <Null> <Null> <Null> <Null>
PRDDB4 20140917 <Null> <Null>
<Null> <Null> 75.80 <Null> <Null>
DESIRED OUTPUT:
HOST DB_NAME TABLESPACE DAY1 DAY2 DAY3 DAY4
DAY5 DAY6 DAY7
------- ---------------- ------------------ ------- ------- ------- -------
------- ------- -------
PRDDB4 PEDW ETL_DATA 76.28 75.82 75.21
<Null> 75.80 <Null> <Null>
--
Thank you.
Sandy
GHX
Other related posts:
