CODEWITHSUNDEEP
sqloracle-databasemergerows
kdragon
kdragon

Reputation: 41

SQL to merge rows in Oracle

I want to merge some rows.

First, my table and data exist as below

GRP     CRRO_NO  TYPE_CD  TYPE_ID
PERSON  1111     FATHER   Tom
PERSON  1111     MOTHER   Jennifer
PERSON  1111     JOB_     Teacher
PERSON  1111     FRIEND   Jimmy
PERSON  1111     FRIEND   Kim
PERSON  1111     FRIEND   Michael

And I want to get the result like

GRP     CRRO_NO   FATHER    MOTHER     JOB_      FRIEND
PERSON  1111      Tom       Jennifer   Teacher   Jimmy
PERSON  1111      Tom       Jennifer   Teacher   Kim
PERSON  1111      Tom       Jennifer   Teacher   Michael

With this situation, how to write SQL?

I've been trying like

SELECT  T1.GRP_CD GRP, T1.CRRO_NO CRRO
      , MAX(T1.MOTHER) MOTHER, MAX(T1.FATHER) FATHER, MAX(T1.JOB_) JOB, T1.FRIEND FRIEND     
  FROM (
        SELECT  DISTINCT
                GRP_CD
               ,CRRO_NO                        
               ,CASE WHEN TYPE_CD  = 'FATHER'  THEN TYPE_ID   ELSE '' END FATHER
               ,CASE WHEN TYPE_CD  = 'MOTHER'  THEN TYPE_ID   ELSE '' END MOTHER
               ,CASE WHEN TYPE_CD  = 'JOB_'    THEN TYPE_ID    ELSE '' END JOB_
               ,CASE WHEN TYPE_CD  = 'FRIEND'  THEN TYPE_ID   ELSE '' END FRIEND
         FROM    TMP     
        WHERE   1=1   AND     TRIM(CRRO_NO) = '1111'
       ) T1
  WHERE T1.CRRO_NO = '1111'
        GROUP BY T1.GRP_CD, T1.CRRO_NO, T1.FRIEND
;

Then, the result of sql above is

GRP      CRRO_NO  MOTHER     FATHER    JOB       FRIEND   
PERSON   1111     Jennifer   Tom       Teacher   (null)
PERSON   1111     (null)     (null)    (null)    Jimmy
PERSON   1111     (null)     (null)    (null)    Kim
PERSON   1111     (null)     (null)    (null)    Michael

Now I know why the result of my query is wrong(not the one I want), but still I can't find the way.

Actually, there are strict rules to classify which one would be duplicated (Mother, Father, Job) and which would not (friend)

For some reasons, I can't modify the structure of my table. (I've changed contents of this question from my business terms and simplified the situation to explain)

Please give any advice to try again

Upvotes: 1

Views: 5080

Answers (3)

dandarc
dandarc

Reputation: 678

Just to give another way of doing this.

with MFJQuery as (
select * from ( 
            select
                   A.GRP,
                   A.CRRO_NO,
                   A.TYPE_CD,
                   A.TYPE_ID
            from test_data A
            where A.TYPE_CD <> 'FRIEND')
pivot
(
    max(TYPE_ID)
    for TYPE_CD in ('MOTHER' as "MOTHER", 
                    'FATHER' as "FATHER", 
                    'JOB_' as "JOB")
))
select A.GRP, A.CRRO_NO, B.MOTHER, B.FATHER, B.JOB, A.TYPE_ID as FRIEND
from test_data A inner join
        MFJQuery B on A.GRP = B.GRP 
        and A.CRRO_NO = B.CRRO_NO
where
    A.TYPE_CD = 'FRIEND'

Upvotes: 0

Jair Hernandez
Jair Hernandez

Reputation: 494

A self-joined table will do the trick:

select distinct t1.GRP, t1.CRRO_NO, t2.TYPE_ID as father, t3.TYPE_ID as mother, t4.TYPE_ID as JOB_, t5.TYPE_ID as FRIEND
from TMP t1
inner join TMP t2   on  t2.CRRO_NO = t1.CRRO_NO 
                    and t2.TYPE_CD = 'FATHER'
inner join TMP t3   on  t3.CRRO_NO = t1.CRRO_NO 
                    and t3.TYPE_CD = 'MOTHER'
inner join TMP t4   on  t4.CRRO_NO = t1.CRRO_NO 
                    and t4.TYPE_CD = 'JOB_'
inner join TMP t5   on  t5.CRRO_NO = t1.CRRO_NO 
                    and t5.TYPE_CD = 'FRIEND';

However note that since you're not specifying a filter predicate (a WHERE clause), all results in table TMP will be processed and you'll have duplicate results, hence the use of 'distinct' would be mandatory, and of course it can be discarded if you use a filter such as: WHERE t1.type_cd = 'FATHER'

Upvotes: 0

Boneist
Boneist

Reputation: 23578

Assuming that 'MOTHER', 'FATHER' and 'JOB_' are unique type_cd values, you could achieve your aims like so:

WITH sample_data AS (SELECT 'PERSON' grp, 1111 crro_no, 'FATHER' type_cd, 'Tom' type_id FROM dual UNION ALL
                     SELECT 'PERSON' grp, 1111 crro_no, 'MOTHER' type_cd, 'Jennifer' type_id FROM dual UNION ALL
                     SELECT 'PERSON' grp, 1111 crro_no, 'JOB_' type_cd, 'Teacher' type_id FROM dual UNION ALL
                     SELECT 'PERSON' grp, 1111 crro_no, 'FRIEND' type_cd, 'Jimmy' type_id FROM dual UNION ALL
                     SELECT 'PERSON' grp, 1111 crro_no, 'FRIEND' type_cd, 'Kim' type_id FROM dual UNION ALL
                     SELECT 'PERSON' grp, 1111 crro_no, 'FRIEND' type_cd, 'Michael' type_id FROM dual)
-- end of mimicking a table called "sample_data" with your data in it.
-- see the SQL below:
SELECT grp,
       crro_no,
       father,
       mother,
       job_,
       type_id friend
FROM   (SELECT grp,
               crro_no,
               type_cd,
               type_id,
               MAX(CASE WHEN type_cd = 'FATHER' THEN type_id END) OVER (PARTITION BY grp, crro_no) father,
               MAX(CASE WHEN type_cd = 'MOTHER' THEN type_id END) OVER (PARTITION BY grp, crro_no) mother,
               MAX(CASE WHEN type_cd = 'JOB_' THEN type_id END) OVER (PARTITION BY grp, crro_no) job_
        FROM   sample_data)
WHERE  type_cd = 'FRIEND';

GRP       CRRO_NO FATHER   MOTHER   JOB_     FRIEND
------ ---------- -------- -------- -------- --------
PERSON       1111 Tom      Jennifer Teacher  Michael
PERSON       1111 Tom      Jennifer Teacher  Jimmy
PERSON       1111 Tom      Jennifer Teacher  Kim

This works by using the MAX() analytic function to output the type_id for the father, mother and job type_cd's in separate columns across all rows.

Then it's simply a matter of filtering the rows to show just the type_cd = 'FRIEND' rows.

Upvotes: 1

Related Questions