7 Replies Latest reply: Dec 5, 2012 4:53 AM by user9093300 RSS

    get data dynamically in sql

    user9093300
      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
          Read FAQ: {message:id=9360005}

          Check dynamic pivoting..
          • 2. Re: get data dynamically in sql
            user9093300
            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
              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
                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
                  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
                    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
                      Did you check the section "Dynamic Pivoting", in the Posted link to FAQ?