Re: Need help converting rows to columns
- From: Kim Berg Hansen <kibeha@xxxxxxxxx>
- To: sbecker6925@xxxxxxxxx
- Date: Mon, 22 Sep 2014 15:58:48 +0200
I assume from your output that column INSERT_DT do not have any time
portion?
PIVOT is a nice way to do things like this, for example:
SELECT *
FROM (
SELECT
host,
db_name,
tablespace,
insert_dt - trunc(sysdate-6) + 1 dayno,
pct_full
FROM ghx_apex_apps.ts_freespace
WHERE insert_dt >= trunc(sysdate-6)
AND pct_full >= 75
AND tablespace = 'ETL_DATA'
) PIVOT (
max(pct_full) as pct
for dayno in (
1 as day1,
2 as day2,
3 as day3,
4 as day4,
5 as day5,
6 as day6,
7 as day7
)
)
ORDER BY
db_name,
tablespace;
Regards
Kim Berg Hansen
http://dspsd.blogspot.com
kibeha@xxxxxxxxx
@kibeha
On Mon, Sep 22, 2014 at 3:31 PM, Sandra Becker <sbecker6925@xxxxxxxxx>
wrote:
> 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:
