This discussion is archived
7 Replies Latest reply: Dec 5, 2012 2:53 AM by user9093300 RSS

get data dynamically in sql

user9093300 Newbie
Currently Being Moderated
Hi in the below SQL I'm pulling data manually(by entering specialist name) instead of that I want this query to be modified so that the sys is pulled dynamically, I.e. like select specialist from t. i use Oracle 10g not 11.

SELECT
type, Hari,Rakesh,Santhosh, Total
FROM (
select
(type), max( decode( SPECIALIST, 'Hari', cnt, null ) ) Hari,
max( decode( SPECIALIST, 'Rakesh', cnt, null ) ) Rakesh,
max( decode( SPECIALIST, 'Santhosh', cnt, null ) ) Santhosh,
Sum(cnt) total
from (
select
(type),specialist, sum(update_count) cnt
from t
group by (type),specialist
) q
group by (type)

Union

Select
'Total' as type, Sum(Hari) Hari,Sum(Rakesh) Rakesh,
Sum(Santhosh) Santhosh,Sum(total) Total
from (
select
(type), max( decode( SPECIALIST, 'Hari', cnt, null ) ) Hari,
max( decode( SPECIALIST, 'Rakesh', cnt, null ) ) Rakesh,
max( decode( SPECIALIST, 'Santhosh', cnt, null ) ) Santhosh, Sum(cnt) total
from (
select (type),specialist, sum(update_count) cnt
from t group by (type),specialist
) q
group by (type)
)a
) b Order by Total



Thanks
  • 1. Re: get data dynamically in sql
    jeneesh Guru
    Currently Being Moderated
    Read FAQ: {message:id=9360005}

    Check dynamic pivoting..
  • 2. Re: get data dynamically in sql
    user9093300 Newbie
    Currently Being Moderated
    Hi Jeenesh,

    Thanks for the reply. I think oracle 10g doesn't support 'PIVOT' keyword. i want an alternative of this as i don't want anyone else to touch the sql part once it is done. please help me with how do i eliminate using max(decode) and get the data dynamically.

    Thanks
  • 3. Re: get data dynamically in sql
    user9093300 Newbie
    Currently Being Moderated
    Hi ,
    i don't think that oracle 10g supports Pivot Keyword. could anyone help me how to eliminate the keyword max(Decode) and get data dynamically.

    Thanks
  • 4. Re: get data dynamically in sql
    jeneesh Guru
    Currently Being Moderated
    To use PIVOT or MAX(DECODE..., you ned to know the distinct values to be grouped before executing the query...

    For dynamic number of columns, you ned to use dynamic SQL, samples are availabel in the posted link..
  • 5. Re: get data dynamically in sql
    BluShadow Guru Moderator
    Currently Being Moderated
    user9093300 wrote:
    Hi ,
    i don't think that oracle 10g supports Pivot Keyword. could anyone help me how to eliminate the keyword max(Decode) and get data dynamically.

    Thanks
    Yes, 10g doesn't have the PIVOT keyword.
    But to get 10g to pivot data you have to use one of the methods described in the FAQ link jeneesh provided.

    In the FAQ there is an article on SQL projection, which explains why the column names have to be known before any data is fetched. The only way to get dynamic column names is to write code that generates the SQL dynamically, assigning the column names to the query and then executing that dynamically generated query to obtain the data. You then have the problem that, if the column names are generated at runtime, you can't have anything that easily uses the results of that query, certainly not if they need to reference the columns by name, and not without writing more dynamic code. (You can use the DBMS_SQL package to reference columns by position, but it seems over the top for most basic requirements people have).

    In truth, the need to dynamically pivot data is not one best suited to SQL. It is more a 'display' issue and one best suited to reporting tools, which query the data first and then process the data to pivot it and layout the results with dynamic column headings or whatever is required.
  • 6. Re: get data dynamically in sql
    user9093300 Newbie
    Currently Being Moderated
    Hi Jeenesh,

    i'm able to use MAX(Decode...), but here i'm needed to enter the values manually, i want it to automated or dynamic.

    Thanks.
  • 7. Re: get data dynamically in sql
    jeneesh Guru
    Currently Being Moderated
    Did you check the section "Dynamic Pivoting", in the Posted link to FAQ?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points