Combining rows

user626688 · Oct 20, 2008 7:43PM

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

Frank Kulash · Oct 20, 2008 8:02PM

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 · Oct 20, 2008 8:14PM

Thank you.. I will try

Walter Fernández · Oct 20, 2008 8:59PM

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,

Forum Stats

Quick Links

Categories

Combining rows

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

Combining rows

Answers