Need help converting rows to columns

From: Sandra Becker <sbecker6925_at_gmail.com>
Date: Mon, 22 Sep 2014 07:31:11 -0600
Message-ID: <CAJzM94ATA+bdXGq_SXJA+9p+sB38vca3LhioDuOm-HML8KMpyQ_at_mail.gmail.com>



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

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Sep 22 2014 - 15:31:11 CEST

Original text of this message