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.
Advertisement
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