9 Replies Latest reply: Jan 30, 2013 9:50 AM by 973995 RSS

    How to pivot duplex column?

    973995
      Hello All,

      I want yo use duplex column in pivoting. However, The following code is working fine however, I don't know how to do it duplex column pivoting, Should I add an extra column name to for clause?
      with 
      pivot_tab as (select month, stat_id, error_id  from qc_t q join surveys s on q.survey_id = s.survey_id
      where qa = 1 and q.error_id in (1, 2, 3, 5, 8, 9, 20)) 
      select * from pivot_tab 
      pivot (
      count(*) for (error_id) in (1, 2, 3, 5, 8, 9, 20)) 
      order by 1, 2;
      
      ----------------------------------------------------------------------------
      
      Output:
      
                      col1 col2 col3 col4 col5 col6       col1 col2 col3 col4 col5 col6       col1 col2 col3 col4 col5 col6    
      stat_id 
      100          2   3  4  2  8  1    1                  1   1   1  1  1  1  1                         2   4  5 2 5 2     3 
      101           2   2  3  1  4  1  4                     2   2  4 1   4   1                         3    5   6   2   1   1   
      102          ............
      .
      .
      .
      I use the following but it doesn't work
      >
      pivot_tab as (select month, stat_id, error_id from qc_t q join surveys s on q.survey_id = s.survey_id
      where qa = 1 and q.error_id in (1, 2, 3, 5, 8, 9, 20))
      select * from pivot_tab
      pivot (
      count(*) for (month, error_id) in (1, 2, 3, 5, 8, 9, 20))
      order by 1, 2;
      >



      I just want the following
      Sample:
      
      
                              first_col1                              first_col2                         first_col3
                      col1 col2 col3 col4 col5 col6       col1 col2 col3 col4 col5 col6       col1 col2 col3 col4 col5 col6    
      stat_id 
      100          2   3  4  2  8  1    1                  1   1   1  1  1  1  1                         2   4  5 2 5 2     3 
      101           2   2  3  1  4  1  4                     2   2  4 1   4   1                         3    5   6   2   1   1   
      102          ............
      .
      .
      .
        • 1. Re: How to pivot duplex column?
          Centinul
          Please review: {message:id=9360002}
          • 2. Re: How to pivot duplex column?
            973995
            >
            Please review: 2. How do I ask a question on the forums?
            >

            I did read and I do know how to ask a question on the forums, and it is appropriate.
            • 3. Re: How to pivot duplex column?
              Centinul
              970992 wrote:
              I did read and I do know how to ask a question on the forums, and it is appropriate.
              Well the thing is to get a reasonable response you have to help us help you. A few piece of information are missing (items 5, 6, and 7 from the link I provided), such as:

              1. Oracle version (I know you're using a version of 11.x because of the PIVOT clause but that's not the point. We shouldn't have to guess).
              SELECT * FROM v$version;
              2. Sample data in the form of CREATE TABLE / INSERT statements so we can easily work with your data without having to generate it ourselves.
              • 4. Re: How to pivot duplex column?
                Solomon Yakobson
                You must use tuples:
                SQL> select  *
                  2    from  (
                  3           select  job,
                  4                   deptno,
                  5                   sal
                  6             from  emp
                  7          ) pivot (sum(sal) for job in ('CLERK','SALESMAN','MANAGER'))
                  8  /
                
                    DEPTNO    'CLERK' 'SALESMAN'  'MANAGER'
                ---------- ---------- ---------- ----------
                        30        950       5600       2850
                        20       1900                  2975
                        10       1300                  2450
                
                SQL> select  *
                  2    from  (
                  3           select  job,
                  4                   deptno,
                  5                   sal
                  6             from  emp
                  7          ) pivot (sum(sal) for (job,deptno) in (('CLERK',20),('SALESMAN',30),('MANAGER',10)))
                  8  /
                
                'CLERK'_20 'SALESMAN'_30 'MANAGER'_10
                ---------- ------------- ------------
                      1900          5600         2450
                
                SQL> 
                SY.
                • 5. Re: How to pivot duplex column?
                  973995
                  >

                  1. Oracle version

                  SELECT * FROM v$version;

                  >

                  Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
                  PL/SQL Release 11.2.0.1.0 - Production
                  "CORE     11.2.0.1.0     Production"
                  TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
                  NLSRTL Version 11.2.0.1.0 - Production
                  • 6. Re: How to pivot duplex column?
                    Solomon Yakobson
                    Solomon Yakobson wrote:
                    You must use tuples:
                    So in your case, something like:

                    for (month, error_id) in ((1,1), (1,2), (1,3), (1,5), (1,8), (1,9), (1,20),(2,1)...))
                    • 7. Re: How to pivot duplex column?
                      973995
                      So, it can only be represented horizontal direction?
                      • 8. Re: How to pivot duplex column?
                        Solomon Yakobson
                        970992 wrote:
                        So, it can only be represented horizontal direction?
                        Not sure what you mean. Each tuple represents a combination of FOR clause columns. In your case it is a combination of month and error_id.

                        SY.
                        • 9. Re: How to pivot duplex column?
                          973995
                          Thanks a lot Solomon for everything.