Re: Need help converting rows to columns
- From: Sandra Becker <sbecker6925@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 22 Sep 2014 09:47:36 -0600
With a minor tweak of TRUNC(insert_dt), this query gave me exactly what I
needed. Thanks for the help.
Sandy
On Mon, Sep 22, 2014 at 7:58 AM, Kim Berg Hansen <kibeha@xxxxxxxxx> wrote:
> 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
>>
>
>
--
Sandy
GHX
Other related posts:
