Selecting values from different columns in rows with the same ID

I have a table called COURSE_SELECT that shows students and which online products they will be able to access, but each product is in a different row, even when each of them has their own column.

Table: COURSE_SELECT

+-----------+-----------+-----------+-----------+-----------+
| StudentID | Product_A | Product_B | Product_C | Product_D |
+-----------+-----------+-----------+-----------+-----------+
|   100     |     Y     |     N     |     N     |     N     |
+-----------+-----------+-----------+-----------+-----------+
|   100     |     N     |     Y     |     N     |     N     |
+-----------+-----------+-----------+-----------+-----------+
|   101     |     N     |     N     |     Y     |     N     |
+-----------+-----------+-----------+-----------+-----------+

The vendor needs me to send a CSV file with only one row per student, like this:

+-----------+-----------+-----------+-----------+-----------+
| StudentID | Product_A | Product_B | Product_C | Product_D |
+-----------+-----------+-----------+-----------+-----------+
|   100     |     Y     |     Y     |     N     |     N     |
+-----------+-----------+-----------+-----------+-----------+
|   101     |     N     |     N     |     Y     |     N     |
+-----------+-----------+-----------+-----------+-----------+

I have no idea how to accomplish this. Any guidance will be greatly appreciated.

Answer

Since Y is greater than N, I’d group by the StudentID and query the maximum of each product:

SELECT   StudentID,
         MAX(Product_A) AS Product_A,
         MAX(Product_B) AS Product_B,
         MAX(Product_C) AS Product_C,
         MAX(Product_D) AS Product_D
FROM     mytable
GROUP BY StudentID

Advertisement

Answer