Forum Stats

  • 3,853,806 Users
  • 2,264,277 Discussions
  • 7,905,450 Comments

Discussions

Combining rows

user626688
user626688 Member Posts: 416 Bronze Badge
edited Oct 20, 2008 8:59PM in SQL & PL/SQL
Guys,

I have the followning requirement. Please see the follwing eg.

I have the following rows and columns in a table


ID CLASS PRODUCT CATEGORY SALE REBATES

1001 A4T ABC W_SALE 100 23
1001 A4T ABC RETAIL 345 45
1003 B6Y XYZ W_SALE 676 123
1004 V8U CVZ RETAIL 500 110


I want to insert the data inot another table .

If there are two rows with same ID,CLASS and PRODUCT but with different CATEGORY then I want to combine these two rows and put the respctive values of
two different category into additional columns.

The ideal output should be as given below

ID CLASS PRODUCT W_SALE RET_SALE W_REBATE RET_REBATE
1001 A4T ABC 100 345 23 45 --combined row
1003 B6Y XYZ 676 NULL 123 NULL
1004 V8U CVZ NULL 500 NULL 110

Here the first two rows are combined by taking common ID,CLASS,PRODUCT and then the values
are taken to two additional columns. If there is no two rows with same ID,CLASS and PRODUCT then only the values with respect
to the CATEGORY will go to the respective columns and other columns are kept null.

Is there any way I can do it with some analytical functions.

Thanks in advance
Tagged:

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,497 Red Diamond
    Hi,

    What you want to do is called a pivot.

    Seach for "Pivot", and/or see the script below for the general technique.

    Your problem is slightly more complicated than some, because you want to pivot two columns from each original row, not just one.
    I suggest you start with just one column (e.g., sale), and when you have learned to pivot that into two columns (ret_sale and w_sale), then add the other columns (ret_rebate and w_rebate).

    General instructions:
    --	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}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
  • user626688
    user626688 Member Posts: 416 Bronze Badge
    Thank you.. I will try
  • Walter Fernández
    Walter Fernández Member Posts: 1,539 Bronze Trophy
    hi,
    SELECT ID,
           CLASS,
           PRODUCT,
           MAX(decode(seq,
                      1,
                      sales,
                      NULL)) W_SALE,
           MAX(decode(seq,
                      2,
                      sales,
                      NULL)) RET_SALE,
           MAX(decode(seq,
                      1,
                      rebates,
                      NULL)) W_REBATE,
           MAX(decode(seq,
                      2,
                      rebates,
                      NULL)) RET_REBATE
      FROM (SELECT id,
                   CLASS,
                   product,
                   sales,
                   rebates,
                   row_number() OVER(PARTITION BY id, CLASS, product ORDER BY T.ID, T.CLASS, T.PRODUCT DESC NULLS LAST) seq
              FROM t)
     WHERE seq <= 4
     GROUP BY id,
              CLASS,
              product;
    T is the first table.

    Regards,
This discussion has been closed.