Forum Stats

  • 3,854,962 Users
  • 2,264,441 Discussions
  • 7,905,847 Comments

Discussions

Piovt?

BobH90024
BobH90024 Member Posts: 25
edited Oct 23, 2008 4:33PM in SQL & PL/SQL
I have a table that looks like this:
SystemId          QuestNum            PassRate

1234                    1                        50
1234                    2                        60
1234                   ...                       ...
9876                    1                         20
9876                    2                          30 
I need output that looks like
SystemId             QuestNum               PassRate             QuestNum           PassRate     ...

1234                        1                           50                      2                       60
9876                        1                           20                      2                       30
I don't have 11g so I cant use PIVOT. Is there a way to do it in 10?
Tagged:

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,506 Red Diamond
    edited Oct 23, 2008 4:30PM Answer ✓
    Hi.

    Your specific problem differs from the generic solution I posted in a couple of ways.

    Instead of COUNT, you want to use MAX (or MIN: if there will be, at most, one input row that fits into each output cell, its value will be both the MIN and the MAX).

    Isotope's solution (which is tailored to your problem) uses the same technique as mine.
    Isotope prefers DECODE, which is a little more concise in this example.
    I prefer CASE, which is easier to understand.
    They're basically the same.

Answers

  • Nicolas Gasparotto
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    It is easy if you know the number of question, it'll become a nightmare if not.

    Nicolas.
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,506 Red Diamond
    edited Oct 23, 2008 3:21PM
    Hi,

    For examples of how to do pivots in Oracle 10 (and earlier), see the two scripts below.
    (1) Is for the situation in which you know beforehand what the different values of QuestNum are.
    (2) Is for the situation when the query itself has to figure out how many different values there are, and what they are. It assumes you know how to do (1).

    (1)
    --	How to Pivot a Result Set (Display Rows as Columns)
    
    --	For Oracle 10, and earlier
    --	This works in any version of Oracle, but the 
    --	"SELECT ... PIVOT" feature introduced in Oracle 11
    --	is better.
    
    --	This example uses the scott.emp table.
    --	Given a query that produces three rows for every department,
    --	how can we show the same data in a query that has one row
    --	per department, and three separate columns?
    
    --	For example, the query below counts the number of employess
    --	in each departent that have one of three given jobs:
    
    PROMPT	==========  0. Simple COUNT ... GROUP BY  ==========
    
    SELECT	deptno
    ,	job
    ,	COUNT (*)	AS cnt
    FROM	scott.emp
    WHERE	job	IN ('ANALYST', 'CLERK', 'MANAGER')
    GROUP BY	deptno
    ,		job;
    
    /*
    Output:
    
        DEPTNO JOB              CNT
    ---------- --------- ----------
            20 CLERK              2
            20 MANAGER            1
            30 CLERK              1
            30 MANAGER            1
            10 CLERK              1
            10 MANAGER            1
            20 ANALYST            2
    */
    
    
    PROMPT	==========  1. Pivot  ==========
    
    SELECT	deptno
    ,	COUNT (CASE WHEN job = 'ANALYST' THEN 1 END)	AS analyst_cnt
    ,	COUNT (CASE WHEN job = 'CLERK'   THEN 1 END)	AS clerk_cnt
    ,	COUNT (CASE WHEN job = 'MANAGER' THEN 1 END)	AS manager_cnt
    FROM	scott.emp
    WHERE	job	IN ('ANALYST', 'CLERK', 'MANAGER')
    GROUP BY	deptno;
    
    
    /*
    --	Output:
    
        DEPTNO ANALYST_CNT  CLERK_CNT MANAGER_CNT
    ---------- ----------- ---------- -----------
            30           0          1           1
            20           2          2           1
            10           0          1           1
    
    
    --	Explanation
    
    (1) Decide what you want the output to look like.
    	(E.g. "I want a row for each department,
    	and columns for deptno, analyst_cnt, clerk_cnt and manager_cnt)
    (2) Get a result set where every row identifies which row
    	and which column of the output will be affected.
    	In the example above, deptno identifies the row, and
    	job identifies the column.
    	Both deptno and job happened to be in the original table.
    	That is not always the case; sometimes you have to
    	compute new columns based on the original data.
    (3) Use aggregate functions and CASE (or DECODE) to produce
    	the pivoted columns.  
    	The CASE statement will pick
    	only the rows of raw data that belong in the column.
    	If each cell in the output corresponds to (at most)
    	one row of input, then you can use MIN or MAX as the
    	aggregate function.
    	If many rows of input can be reflected in a single cell
    	of output, then use SUM, COUNT, AVG, STRAGG, or some other 
    	aggregate function.
    	GROUP BY the column that identifies rows.
    */
    {code}
    
    (2) 
    {code}
    /*
    How to Pivot a Table with a Dynamic Number of Columns
    
    For example, you want to make a cross-tab output of
    the scott.emp table.
    Each row will represent a department.
    There will be a separate column for each job.
    Each cell will contain the number of employees in
    	a specific department having a specific job.
    The exact same solution must work with any number
    of departments and columns.
    (Within reason: there's no guarantee this will work if you 
    want 2000 columns.)
    */
    
    
    PROMPT	==========  0. Basic Pivot  ==========
    
    SELECT	deptno
    ,	COUNT (CASE WHEN job = 'ANALYST' THEN 1 END)	AS analyst_cnt
    ,	COUNT (CASE WHEN job = 'CLERK'   THEN 1 END)	AS clerk_cnt
    ,	COUNT (CASE WHEN job = 'MANAGER' THEN 1 END)	AS manager_cnt
    FROM	scott.emp
    WHERE	job	IN ('ANALYST', 'CLERK', 'MANAGER')
    GROUP BY	deptno
    ORDER BY	deptno
    ;
    
    
    PROMPT	==========  1. Dynamic Pivot  ==========
    
    --	*****  Start of dynamic_pivot.sql  *****
    
    -- Suppress SQL*Plus features that interfere with raw output
    SET	FEEDBACK	OFF
    SET	PAGESIZE	0
    
    SPOOL	p:\sql\cookbook\dynamic_pivot_subscript.sql
    
    SELECT	DISTINCT
    	',	COUNT (CASE WHEN job = '''
    ||	job
    ||	''' '	AS txt1
    ,	'THEN 1 END)	AS '
    ||	job
    ||	'_CNT'	AS txt2
    FROM	scott.emp
    ORDER BY	txt1;
    
    SPOOL	OFF
    
    -- Restore SQL*Plus features suppressed earlier
    SET	FEEDBACK	ON
    SET	PAGESIZE	50
    
    SPOOL	p:\sql\cookbook\dynamic_pivot.lst
    
    SELECT	deptno
    @@dynamic_pivot_subscript
    FROM	scott.emp
    GROUP BY	deptno
    ORDER BY	deptno
    ;
    
    SPOOL	OFF
    
    --	*****  End of dynamic_pivot.sql  *****
    
    /*
    EXPLANATION:
    The basic pivot assumes you know the number of distinct jobs,
    and the name of each one.  If you do, then writing a pivot query
    is simply a matter of writing the correct number of ", COUNT ... AS ..."\
    lines, with the name entered in two places on each one.  That is easily
    done by a preliminary query, which uses SPOOL to write a sub-script 
    (called dynamic_pivot_subscript.sql in this example).
    
    The main script invokes this sub-script at the proper point.
    In practice, .SQL scripts usually contain one or more complete
    statements, but there's nothing that says they have to.
    This one contains just a fragment from the middle of a SELECT statement.
    
    Before creating the sub-script, remember to turn off SQL*Plus features 
    that are designed to help humans read the output (such as headings and 
    feedback messages like "7 rows selected.", since we do not want these 
    to appear in the sub-script.
    Remember to turn these features on again before running the main query.
    
    */
    {code}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
    Frank Kulash
  • isotope
    isotope Member Posts: 748 Bronze Badge
    [email protected]>
    [email protected]> --
    [email protected]> with t as (
      2    select 1234 as systemid, 1 as questnum, 50 as passrate from dual union all
      3    select 1234, 2, 60 from dual union all
      4    select 1234, 3, 70 from dual union all
      5    select 9876, 1, 20 from dual union all
      6    select 9876, 2, 30 from dual union all
      7    select 9876, 3, 40 from dual union all
      8    select 9876, 4, 50 from dual)
      9  --
     10  select systemid,
     11         max(decode(questnum,1,questnum)) as questnum1,
     12         max(decode(questnum,1,passrate)) as passrate1,
     13         max(decode(questnum,2,questnum)) as questnum2,
     14         max(decode(questnum,2,passrate)) as passrate2,
     15         max(decode(questnum,3,questnum)) as questnum3,
     16         max(decode(questnum,3,passrate)) as passrate3,
     17         max(decode(questnum,4,questnum)) as questnum4,
     18         max(decode(questnum,4,passrate)) as passrate4
     19    from t
     20   group by systemid
     21   order by systemid;
    
      SYSTEMID  QUESTNUM1  PASSRATE1  QUESTNUM2  PASSRATE2  QUESTNUM3  PASSRATE3  QUESTNUM4  PASSRATE4
    ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
          1234          1         50          2         60          3         70
          9876          1         20          2         30          3         40          4         50
    
    [email protected]>
    [email protected]>
    isotope
    isotope
  • BobH90024
    BobH90024 Member Posts: 25
    Hi,

    For examples of how to do pivots in Oracle 10 (and earlier), see the two scripts below.
    (1) Is for the situation in which you know beforehand what the different values of QuestNum are.
    (2) Is for the situation when the query itself has to figure out how many different values there are, and what they are. It assumes you know how to do (1).

    ...




    I think this is close to what I want but I'm confused about "count". I don't want a count of the values, I just need the values of questnum and passrate to be
    columns in s single row based on systemid.


    ??
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,506 Red Diamond
    edited Oct 23, 2008 4:30PM Answer ✓
    Hi.

    Your specific problem differs from the generic solution I posted in a couple of ways.

    Instead of COUNT, you want to use MAX (or MIN: if there will be, at most, one input row that fits into each output cell, its value will be both the MIN and the MAX).

    Isotope's solution (which is tailored to your problem) uses the same technique as mine.
    Isotope prefers DECODE, which is a little more concise in this example.
    I prefer CASE, which is easier to understand.
    They're basically the same.
  • BobH90024
    BobH90024 Member Posts: 25
    Thanks,

    I think I got it. I went with the non-dynamic solution. At most I'll have 12 QuestNums so it was easier.

    Thanks to both for your answers.

    Cheers.
This discussion has been closed.