CODEWITHSUNDEEP
sqloracle-databaseleft-join
Henri Hietala
Henri Hietala

Reputation: 3041

Oracle SQL merge row values to separate columns

I need help with combining two rows with following requirements (it's easier to demonstrate with tables..):

My schema looks like this:

project table:

project_id  |   name
--------------------------------
1               Project1
2               Project2
3               Project3

project_program table

id  |   project_id  |   program_id
-----------------------------------
1       1               1       
2       1               2   
2       3               2

program table:

program_id  |   type    |   name
---------------------------------------
1               A           test    
2               B           production

desired output for select statement:

project_id  |   name     |  A      |    B
-------------------------------------------------------------------
1               Project1    test        production
2               Project2    (null)      (null)
2               Project3    (null)      production

This is what I've managed to achieve so far:

SELECT 
  project.*,
  CASE WHEN program.type LIKE 'A' THEN program.name ELSE NULL END AS A,
  CASE WHEN program.type LIKE 'B' THEN program.name ELSE NULL END AS B
FROM 
  project
  LEFT JOIN project_program ON project.project_id = project_program.project_id  
  LEFT JOIN program ON project_program.program_id = program.program_id

It's close but not quite. Now my result looks like this:

project_id  |   name     |  A       |   B
-------------------------------------------------------------------
1               Project1    test        (null)
1               Project1    (null)      production
2               Project2    (null)      (null)
3               Project3    (null)      production

I'm using Oracle 11g.

Upvotes: 1

Views: 1205

Answers (1)

Ian Kenney
Ian Kenney

Reputation: 6426

you can probably just agregate what you have - something like: 

SELECT   id, name, max(A) A, max(b) B
FROM  (
  SELECT 
    project.id, Project.name,
    CASE WHEN program.type LIKE 'A' THEN program.name ELSE NULL END AS A,
    CASE WHEN program.type LIKE 'B' THEN program.name ELSE NULL END AS B
  FROM 
    project
    LEFT JOIN project_program ON project.project_id = project_program.project_id  
    LEFT JOIN program ON project_program.program_id = program.program_id 
  ) X 
GROUP BY  id, name

Upvotes: 2

Related Questions