0

I have 2 tables, one which is the master the other the details. They have a 1 to many relationship:

MasterTable
------------
id (pk)
col1


DetailTable
------------
id (pk)
mid (fk to MasterTable id)
col1

An example of how data would look would be as such:

MasterTable

ID   
---
1     
2
3
4
5 


DetailTable

ID | MID   | COL1  |
---|-------|-------|
1     1       BIRD     
2     1       DOG                      
3     1       DOG --Duplicates are allowed for the same MID
4     1       CAT
5     2       CAT
6     5       FISH

For the detail table the possible values for col1 are:

  • BIRD
  • DOG
  • CAT
  • FISH
  • MOUSE

I then need to create a query which will layout for each id in the master table to see if they have any of these values. So the final query result would look like this:

MasterID | BIRD | DOG | CAT | FISH | MOUSE
-----------------------------------------------------
1           Y     Y      Y
2                        Y
3
4                        
5                              Y

So I am collapsing the detail table into one row in the final result. I am unsure how to create this query though.

I was trying something like this, but it doesn't work:

SELECT MASTERTABLE.ID, 
CASE WHEN DETAILTABLE.col1 = 'BIRD' THEN 'Y' ELSE NULL END AS BIRD,
CASE WHEN DETAILTABLE.col1 = 'DOG' THEN 'Y' ELSE NULL END AS DOG, 
CASE WHEN DETAILTABLE.col1 = 'CAT' THEN 'Y' ELSE NULL END AS CAT, 
CASE WHEN DETAILTABLE.col1 = 'FISH' THEN 'Y' ELSE NULL END AS FISH, 
CASE WHEN DETAILTABLE.col1 = 'MOUSE' THEN 'Y' ELSE NULL END AS MOUSE
FROM MASTERTABLE LEFT JOIN DETAILTABLE ON MASTERTABLE.ID = DETAILTABLE.mid;
user2924127
  • 6,034
  • 16
  • 78
  • 136
  • 2
    "Doesn't work" isn't a valid problem statement (although the issue is most likely that it's putting things on different rows, since you're not aggregating anything). In any case, definite duplicate of [Oracle SQL pivot query](https://stackoverflow.com/questions/4841718/oracle-sql-pivot-query) – Clockwork-Muse Oct 13 '17 at 22:18
  • I do not think it is a definite duplicate of this. Thanks for the pivot feature. I have read the documentation and they all seem to show how to work on a single level of tables, but not multiple levels such as a master and detail table. If I just had one table, I understand how to construct the query, but I do not understand how to then use the pivot to take a detail table (which has a 1 to many relationship with the master) and combine these detail rows into one row. – user2924127 Oct 13 '17 at 22:35
  • ..... pivot the detail table, then (`LEFT)` join it to the master table? The primary thing in SQL is that statements are about about data selection/transformation. Every (sub)query manipulates the representation of the data in some way (which can be used to produce some pretty spectacular results). The result of a pivot is going to be another "table", which you can refer to as such. – Clockwork-Muse Oct 13 '17 at 22:40
  • @Clockwork-Muse - that argument works exactly the same way in the reverse order. The result of a left join is a set of rows, just like a single base table is. If we can pivot data from a table, we can also pivot data from the result of a join. So we can do the join first, and then do the pivoting. I chose this approach for my Answer because it was much closer to what the OP was doing already. He/she wasn't that far off, actually. –  Oct 14 '17 at 02:50

1 Answers1

3

Pivoting, whether it is done with the PIVOT operator (starting from Oracle version 11.1) or with conditional aggregation (version 10.2 or earlier), works equally well on any set of rows, whether that is a single table, the result of joining two tables, or the result of any other set operation permitted in SQL (another aggregation, a UNION ALL, or anything else).

You were pretty close in your attempt. All you have to do is to GROUP BY MASTERTABLE.ID (since you want only one row per MASTERTABLE.ID) and to take the MAX of all those CASE expressions: if at least one of them is 'Y' then the MAX is 'Y', otherwise it is NULL.

So:

In your query, EXACTLY AS IT IS,

  • change CASE .... END as BIRD to MAX(CASE ... END) as BIRD
  • add GROUP BY MASTERTABLE.ID at the end