1 2 Previous Next 16 Replies Latest reply: Dec 16, 2011 4:05 PM by 680607 RSS

    Crosstab query using pure SQL only

    450710
      Hi all,

      Found a lot of threads on crosstab, but none seems to address what I need. I need to perform crosstab query using pure SQL only & the number of columns are dynamic. From a query, I obtained the below table:

      Name Date Amount
      Alex 2005-06-10 1000
      Alex 2005-06-20 1000
      Alex 2005-07-10 1000
      Alex 2005-07-20 1000
      Alex 2005-08-10 1000
      Alex 2005-08-20 1000
      John 2005-06-10 2000
      John 2005-06-20 2000
      John 2005-07-10 2000
      John 2005-07-20 2000
      John 2005-08-10 2000
      John 2005-08-20 2000

      And I need to transform it into:

      Name 06-2005 07-2005 08-2005
      Alex 2000 2000 2000
      John 4000 4000 4000

      Reason for the columns being dynamic is because they'll be a limit on the date ranges to select the data from. I'd have a lower & upper bound date say June-2005 to August-2005, which explains how I got the data from the above table.

      Please advise.

      Thanks!
        • 1. Re: Crosstab query using pure SQL only
          Laurent Schneider
          select name, 
          sum(decode(trunc(d,'MM'),date '2005-06-01',amount)) Jun05,
          sum(decode(trunc(d,'MM'),date '2005-07-01',amount)) Jul05,
          sum(decode(trunc(d,'MM'),date '2005-08-01',amount)) Aug05
          from t
          group by name;
          • 2. Re: Crosstab query using pure SQL only
            450710
            Thanks for your reply Laurent, however, I need the columns to be dynamic. Given the lower & upper bound, how would I be able to do it?
            • 3. Re: Crosstab query using pure SQL only
              Laurent Schneider
              there is no such thing! a sql query has a fix number of columns.

              but why not trying with cursor :
              select deptno, cursor(select to_char(hiredate,'MM-YYYY') MM, sum(sal)
                    from emp where dept.deptno = deptno
                    group by to_char(hiredate,'MM-YYYY')) "CURSOR"
              from dept;
              
                  DEPTNO CURSOR
              ---------- --------------------
                      10 CURSOR STATEMENT : 2
              
              CURSOR STATEMENT : 2
              MM        SUM(SAL)
              ------- ----------
              01-1982       1300
              06-1981       2450
              11-1981       5000
                      20 CURSOR STATEMENT : 2
              
              CURSOR STATEMENT : 2
              MM        SUM(SAL)
              ------- ----------
              04-1981       2975
              04-1987       3000
              05-1987       1100
              12-1980        800
              12-1981       3000
                      30 CURSOR STATEMENT : 2
              
              CURSOR STATEMENT : 2
              MM        SUM(SAL)
              ------- ----------
              02-1981       2850
              05-1981       2850
              09-1981       2750
              12-1981        950
                      40 CURSOR STATEMENT : 2
              
              CURSOR STATEMENT : 2
              • 4. Re: Crosstab query using pure SQL only
                450710
                In PL, we could do something like this:
                for i in 1 .. trunc(p_end)-trunc(p_start)+1
                for iteration. Is there a similar mechanism for SQL?

                Unfortunately the CURSOR method you mentioned can't help me get the data format (crosstab) that I need.

                Thanks!
                • 5. Re: Crosstab query using pure SQL only
                  Laurent Schneider
                  yes, you can try xquery if you have 10gR2
                  also note that you can use DBMS_OUTPUT and PL/SQL

                  HTH
                  Laurent
                  • 6. Re: Crosstab query using pure SQL only
                    450710
                    Unfortunately I'm using Oracle9i and I don't forsee changing to Oracle 10g in this year. Would love to use PL but it would cause me more issues as the query is merely part of a bigger picture.

                    Thanks for your kind assistance Laurent!
                    • 7. Re: Crosstab query using pure SQL only
                      245482
                      If you know that there won't be more than, for example, six months then you can pivot your data by month number:
                      drop table junk;
                      
                      create table junk (
                        name varchar2(30),
                        dt date,
                        amount number );
                      
                      insert into junk values('Alex', to_date('2005-06-10','yyyy-mm-dd'), 1000);
                      insert into junk values('Alex', to_date('2005-06-20','yyyy-mm-dd'), 1000);
                      insert into junk values('Alex', to_date('2005-07-10','yyyy-mm-dd'), 1000);
                      insert into junk values('Alex', to_date('2005-07-20','yyyy-mm-dd'), 1000);
                      insert into junk values('Alex', to_date('2005-08-10','yyyy-mm-dd'), 1000);
                      insert into junk values('Alex', to_date('2005-08-20','yyyy-mm-dd'), 1000);
                      insert into junk values('John', to_date('2005-06-10','yyyy-mm-dd'), 2000);
                      insert into junk values('John', to_date('2005-06-20','yyyy-mm-dd'), 2000);
                      insert into junk values('John', to_date('2005-07-10','yyyy-mm-dd'), 2000);
                      insert into junk values('John', to_date('2005-07-20','yyyy-mm-dd'), 2000);
                      insert into junk values('John', to_date('2005-08-10','yyyy-mm-dd'), 2000);
                      insert into junk values('John', to_date('2005-08-20','yyyy-mm-dd'), 2000);
                      
                      commit;
                      
                      SELECT name,
                             sum(decode(month_num, 0, amount)) AS month1,
                             sum(decode(month_num, 1, amount)) AS month2,
                             sum(decode(month_num, 2, amount)) AS month3,
                             sum(decode(month_num, 3, amount)) AS month4,
                             sum(decode(month_num, 4, amount)) AS month5,
                             sum(decode(month_num, 5, amount)) AS month6
                        FROM (
                      SELECT name,
                             MONTHS_BETWEEN (TRUNC (dt, 'mm'),
                                             MIN (TRUNC (dt, 'mm')) OVER (PARTITION BY NULL)
                             ) AS month_num,
                             amount
                        FROM junk
                             )
                       GROUP BY 
                             name;
                      • 8. Re: Crosstab query using pure SQL only
                        jfuda
                        Hi,

                        I couldn't resist the intellectual challenge of a pure SQL solution for a pivot table with a dynamic number of columns. As Laurent pointed out, a SQL query can only have a fixed number of columns. You can fake a dynamic number of columns, though, by selecting a single column containing data at fixed positions.
                        <br>
                        <br>
                        If it were me, I'd use a PL/SQL solution, but if you must have a pure SQL solution, here is an admittedly gruesome one. It shows the sum of all EMP salaries per department over a date range defined by start and end date parameters (which I've hardcoded for simplicity). Perhaps some of the techniques demonstrated may help you in your situation.
                        <br>
                        <br>
                        set echo off
                        set heading on
                        set linesize 100
                        <br>
                        select version from v$instance ;
                        <br>
                        set heading off
                        <br>
                        column sort_order noprint
                        column sal_sums format a80
                        <br>
                        select -- header row
                          1        as sort_order,
                          'DEPTNO' as DEPTNO ,
                          sys_connect_by_path
                            ( rpad
                                ( to_char(month_column),
                                  10
                                ),
                              ' | '
                            ) as sal_sums
                        from
                          (
                            select
                              add_months( first_month, level - 1 ) as month_column
                            from
                              ( select
                                  date '1981-01-01' as first_month,
                                  date '1981-03-01' as last_month,
                                  months_between( date '1981-03-01', date '1981-01-01' ) + 1 total_months
                                from dual
                              )
                            connect by level < total_months + 1
                          ) months
                        where
                          connect_by_isleaf = 1
                        connect by
                          month_column = add_months( prior month_column, 1 )
                        start with
                          month_column = date '1981-01-01'
                        union all
                        select -- data rows
                          2 as sort_order,
                          deptno,
                          sys_connect_by_path( sum_sal, ' | ' ) sal_sums
                        from
                        (
                          select
                            dept_months.deptno,
                            dept_months.month_column,
                            rpad( to_char( nvl( sum( emp.sal ), 0 ) ), 10 ) sum_sal
                          from
                            (
                              select
                                dept.deptno,
                                reporting_months.month_column
                              from
                                dept,
                                ( select
                                    add_months( first_month, level - 1 ) as month_column
                                  from
                                    ( select
                                        date '1981-01-01' as first_month,
                                        date '1981-03-01' as last_month,
                                        months_between( date '1981-03-01', date '1981-01-01' ) + 1 total_months
                                      from
                                        dual
                                    )
                                  connect by level < total_months + 1
                                ) reporting_months
                            ) dept_months,
                            emp
                          where
                            dept_months.deptno = emp.deptno (+) and
                            dept_months.month_column = trunc( emp.hiredate (+), 'MONTH' )
                          group by
                            dept_months.deptno,
                            dept_months.month_column
                        ) dept_months_sal
                        where
                          month_column = date '1981-03-01'
                        connect by
                          deptno = prior deptno and
                          month_column = add_months( prior month_column, 1 )
                        start with
                          month_column = date '1981-01-01'
                        order by
                          1, 2
                        ;
                        <br>
                        VERSION
                        -----------------
                        10.1.0.3.0
                        <br>
                        DEPTNO      | 81-01-01   | 81-02-01   | 81-03-01
                        10          | 0          | 0          | 0
                        20          | 0          | 0          | 0
                        30          | 0          | 2850       | 0
                        40          | 0          | 0          | 0
                        <br>
                        Now, if we substitute '1981-03-01' with '1981-06-01', we see 7 columns instead of 4
                        <br>
                        DEPTNO      | 81-01-01   | 81-02-01   | 81-03-01   | 81-04-01   | 81-05-01   | 81-06-01
                        10          | 0          | 0          | 0          | 0          | 0          | 2450
                        20          | 0          | 0          | 0          | 2975       | 0          | 0
                        30          | 0          | 2850       | 0          | 0          | 2850       | 0
                        40          | 0          | 0          | 0          | 0          | 0          | 0
                        <br>
                        To understand the solution, start by running the innermost subquery by itself and then work your way outward.
                        • 9. Re: Crosstab query using pure SQL only
                          ADFBeginer
                          I Get error while running the following query.
                          ERROR at line 1:
                          ORA-01436: CONNECT BY loop in user data

                          select
                          dept.deptno,
                          reporting_months.month_column
                          from
                          dept,
                          ( select
                          add_months( first_month, level - 1 ) as month_column
                          from
                          ( select
                          date '1981-01-01' as first_month,
                          date '1981-03-01' as last_month,
                          months_between( date '1981-03-01', date '1981-01-01' ) + 1 total_months
                          from
                          dual
                          )
                          connect by level < total_months + 1
                          ) reporting_months
                          • 10. Re: Crosstab query using pure SQL only
                            Billy~Verreynne
                            > I need to perform crosstab query using pure SQL only & the number of columns are dynamic

                            That is just silly IMO. How do you expect to deal with the SQL projection at runtime? Data should be structured as that is the best way to deal with data programatically.

                            Which means that dynamic columns is out and structured data in the form of arrays/collections are in.

                            Consider..

                            SQL> create or replace type TStrings is table of varchar2(4000);
                            2 /

                            Type created.

                            SQL> select
                            2 t1.object_type,
                            3 count(*) as OBJECT_COUNT,
                            4 CAST( MULTISET(select t2.object_name from user_objects t2 where t2.object_type = t1.object_type) as TStrings ) as NAME_LIST
                            5 from user_objects t1
                            6 group by
                            7* t1.object_type
                            SQL> /

                            OBJECT_TYP OBJECT_COUNT NAME_LIST
                            ---------- ------------ ----------------------------------------------------------------------------------------------------
                            SEQUENCE 1 TSTRINGS('PRODUCT_ID_SEQUENCE')
                            PROCEDURE 4 TSTRINGS('FOO', 'TESTFOR', 'TESTFORALL', 'W')
                            TABLE 6 TSTRINGS('WHAT_ARE_THE_TYPES', 'DEPT', 'EMP', 'BONUS', 'SALGRADE', 'FOO_TAB')
                            INDEX 2 TSTRINGS('PK_DEPT', 'PK_EMP')
                            FUNCTION 2 TSTRINGS('FOOPIPE', 'WORKINGMINUTES')
                            TYPE 6 TSTRINGS('TARRAY2D', 'TARRAY1D', 'TNUMBERARRAY', 'TNAMEVALUE', 'TNAMEVALUEARRAY', 'TSTRINGS')

                            6 rows selected.

                            SQL>
                            .. structured and not mess of unknown and dynamic columns.
                            • 11. Re: Crosstab query using pure SQL only
                              ADFBeginer
                              I am using oracle 8i ver8.1.16.
                              Will this query run in 8i version?. I am getting error due to that may be.
                              • 12. Re: Crosstab query using pure SQL only
                                Billy~Verreynne
                                Oracle 8i? Why do you use an old discontinued and unsupported Oracle version?
                                • 13. Re: Crosstab query using pure SQL only
                                  728403
                                  Hi,
                                  I need you help to solve a problem. I need to create cross tab report and I have a table which has following columns:
                                  State     
                                  City     
                                  PROD_TYPE
                                  Validation Type
                                  Priority
                                  CALC1
                                  CALC2

                                  and the output I need is as :

                                       State          NJ          NJ          NY          NY     
                                       City          A          B          C          D     
                                                 :

                                  PROD_TYPE     Validation Type     Priority     CALC1 :     CALC2:     CALC1 :     CALC2:     CALC1 :     CALC2:     CALC1 :     CALC2                         
                                  FA     Accuracy     HIGH               25     12     18     24
                                  FA     Accuracy                    0     0     0     0
                                  NM     Accuracy                                   
                                  TR     Accuracy                                   
                                  TD     Accuracy     URGENT                                        
                                  TD     Accuracy     HIGH     6     12     9     6
                                  TD     Accuracy     MEDIUM                                        
                                  TD     Accuracy     LOW                                        
                                  TD     Accuracy     INFORMATIONAL                                        
                                  TD     Accuracy          0     0                              
                                  TD     Comprehensiveness     HIGH                                        
                                  TD     Comprehensiveness     INFORMATIONAL                                        
                                  TD     Consistency     HIGH               77     268                    
                                  TD     Consistency     INFORMATIONAL                                        
                                  TD     Format     HIGH                                        
                                  TP     Completeness     URGENT                                        
                                  TP     Completeness     HIGH                                        
                                  TP     Completeness

                                  The Columns CALC1 and CALC2 is number and shows the SUM, and can be increased or decrease in output depends on data. I apreciate and very thankful if you please help me to solve this query.

                                  Thanks & Regards,
                                  Tanish

                                  Edited by: user6153923 on Oct 16, 2009 6:59 AM
                                  • 14. Re: Crosstab query using pure SQL only
                                    Frank Kulash
                                    Hi, Tanish,

                                    Welcome to the forum!

                                    Why don't you start your own thread? Include a link to this thread if you think that will help people.

                                    Whenever you ask a question, include CREATE TABLE and INSERT staements for a little sample data, and the formatted results you want from that data.
                                    When you post pormatted text on this site, type these 6 characters:
                                    (small letters only, inside curly brackets) before and after the formatted sections, otherwise the site will compress the spaces.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                                    1 2 Previous Next