How to get multiple row data into a row with multiple columns

Question

I have a MySQL table like this:

User_Id course_name course_location course_id
1 course name 1 location 1 1
1 course name 2 location 2 2
1 course name 3 location 1 3
2 course name 2 location 1 2
2 course name 4 location 4 4

How can I get data a result like this:

User_id course 1 course2 course3 course4
1 yes-location1 yes-location2 yes-location1 NULL
2 NULL yes-location1 NULL yes-location4

• Jonathan Fite: Which version of MySQL are you using? What you want is called a pivot, this answer here stackoverflow.com/questions/7674786/mysql-pivot-table should show you what you are looking for.

Answer 1

You must Pivot data using GROUP BY with MAX aggregate and use CASE to filter by User_id.

Query:

SELECT User_id
 , MAX(
 CASE WHEN course_id = 1 THEN course_location END
 ) as Course_1
 , MAX(CASE WHEN course_id = 2 THEN course_location END) as Course_2
 , MAX(CASE WHEN course_id = 3 THEN course_location END) as Course_3
 , MAX(CASE WHEN course_id = 4 THEN course_location END) as Course_4
FROM data
GROUP BY User_id;

Sample query in SQL Fiddle.

You can replace course_location by CONCAT('YES-', course_location) is the leading YES is indeed needed.

Output:

User_Id | Course_1 | Course_2 | Course_3 | Course_4
1 | location 1 | location 2 | location 1 | (null)
2 | (null) | location 1 | (null) | location 4

• tmarois: This is an old thread, but what is the performance in doing this, what if I had 15 CASE fields I wanted to inject, and what if there was say 100,00o results in the db, using this method, would it have serious performance issues?

Answer 2

Table Creation:

CREATE TABLE VMRRTEST_DATA (
 ID INTEGER, 
 CATG VARCHAR(30 ), 
 PRICE INTEGER, 
 DATE TIMESTAMP
 )

Insertion:

INSERT INTO VMRRTEST_DATA VALUES(1,'CAR',1000,CURRENT TIMESTAMP);
INSERT INTO VMRRTEST_DATA VALUES(2,'CAR',2000,(CURRENT TIMESTAMP)- 10 MINUTE);
INSERT INTO VMRRTEST_DATA VALUES(3,'CAR',1000,CURRENT TIMESTAMP - 20 minute);
INSERT INTO VMRRTEST_DATA VALUES(4,'CAR',30000,CURRENT TIMESTAMP);
INSERT INTO VMRRTEST_DATA VALUES(5,'CAR',5000,(CURRENT TIMESTAMP)-20 minute);

Result set:

ID CATG PRICE DATE
 -- ---- ----- --------------------------
 1 CAR 1000 2018年10月09日 22:17:59.907636
 4 CAR 30000 2018年10月09日 22:18:32.58254
 2 CAR 2000 2018年10月09日 22:30:30.875961
 3 CAR 1000 2018年10月09日 22:20:43.80537
 5 CAR 5000 2018年10月09日 22:21:14.787224

Expected Result set:

 RANK ID CATG PRICE DATE
 ---- -- ---- ----- --------------------------
 1 3 CAR 1000 2018年10月09日 22:20:43.80537
 1 2 CAR 2000 2018年10月09日 22:30:30.875961
 1 5 CAR 5000 2018年10月09日 22:21:14.787224
 1 4 CAR 30000 2018年10月09日 22:18:32.58254

Solution:

SELECT Rank,ID,CATG,PRICE,DATE FROM 
( 
SELECT
--ROW_NUMBER() OVER(ORDER BY PRICE ASC) AS RW_NM,
RANK() OVER (PARTITION BY PRICE ORDER BY DATE DESC) AS Rank,
ID,CATG,
PRICE
,DATE 
FROM 
VMRRTEST_DATA 
--WHERE Rank 
GROUP BY ID,DATE,CATG,PRICE
HAVING COUNT(*)>0
ORDER BY PRICE,DATE DESC
)
WHERE Rank = 1

• Colin 't Hart: Sorry, but your answer doesn't answer the question.