Sql query to merge multiple records for particular key into single record

Question

I came across the difficult requirement (I am novice to sql, the requirement could be simple as well) where in I need to merge the multiple records available for the particular key in a table to single record(I want to display it in single row).

For example Suppose I have table ABC with following data

Table ABC

  CODE    TRANS_CODE     AMOUNT
    01      01            123
    01      02            456
    01      03            678
    01      04            889

Here I want to display all the records for CODE=01 into single row , like mentioned below

 CODE       AMOUNT1    AMOUNT2   AMOUNT3  AMOUNT4
  01          123        456      678       889

That means for CODE=01 , and TRANS_CODE=01 then the value of AMOUNT needs to be displayed in AMOUNT1 column, similarly for CODE=01 , and TRANS_CODE=02 then the value of AMOUNT needs to be displayed in AMOUNT2 column and so on.

Note : There will be only 5 TRANS_CODE(01,02,03,04,05) so for each CODE there will be 5 records and I need to merge into single record like I mentioned above.

It will be great If I get any idea how to build the sql query for this. I need to build it in Oracle ADF.

Thanks in advance.

possible duplicate of [Oracle SQL pivot query](http://stackoverflow.com/questions/4841718/oracle-sql-pivot-query) — Bulat, Aug 09 '15 at 18:48

score 1 · Answer 1 · answered Aug 09 '15 at 15:40

You can do this with conditional aggregation:

select code,
       max(case when trans_code = '01' then amount end) as amount1,
       max(case when trans_code = '02' then amount end) as amount2,
       max(case when trans_code = '03' then amount end) as amount3,
       max(case when trans_code = '04' then amount end) as amount4,
       max(case when trans_code = '05' then amount end) as amount5
from ABC t
group by code;

score 1 · Answer 2 · answered Aug 09 '15 at 18:58

I prefer PIVOT clause as it explicitly does what you want:

SELECT * FROM 
(
  SELECT 
    Code, 
    Trans_Code,  
    Amount
  FROM
    abc
)
PIVOT (
  SUM(Amount) 
  FOR TRANS_CODE in (
       '01' AS Amount1, 
       '02' AS Amount2, 
       '03' AS Amount3, 
       '04' AS Amount4, 
       '05' AS Amount5)
);

Sql query to merge multiple records for particular key into single record

2 Answers2