How to convert multiple rows into multiple columns based on value

Question

I know it's duplicate, But I'm confused. I'm not good at the database.

I know it can easily be handled from code. But the request is for the query, that's why I'm going through this path.

I have data like below which I get through this query

**SELECT c.CASE_NUMBER,mt.MAIN_TEST_NAME, t.TASK_START, t.TASK_END FROM `CASE` c 
INNER JOIN MAIN_TEST mt ON c.CASE_ID=mt.CASE_ID
INNER JOIN JOB j on c.CASE_ID = j.JOB_ID
INNER JOIN TASK t on j.TASK_ID= t.TASK_ID
WHERE  (c.CREATION_DATE BETWEEN '2019-01-01 00:00:00' AND CURDATE() )** 

CASE NUMBER     MAIN TEST   TASK_START          TASK_END
09232           BX          11/8/2019 14:40     11/8/2019 15:40
09232           FH          11/8/2019 13:35     11/8/2019 15:12
09234           FH          11/6/2019 12:54     11/6/2019 14:54
09234           BX          11/6/2019 11:23     11/6/2019 13:44

I tried the following query,

**SELECT c.CASE_NUMBER,
   (CASE WHEN mt.MAIN_TEST_NAME='BX' THEN t.TASK_START END) as 'BX_START_DATE',
   (CASE WHEN mt.MAIN_TEST_NAME='FH' THEN t.TASK_START END) as 'FH_START_DATE'
FROM `CASE` c
INNER JOIN MAIN_TEST mt ON c.CASE_ID=mt.CASE_ID
INNER JOIN JOB j on c.CASE_ID = j.JOB_ID
INNER JOIN TASK t on j.TASK_ID= t.TASK_ID
WHERE  (c.CREATION_DATE BETWEEN '2019-01-01 00:00:00' AND CURDATE() )**

But in this query, I'm confused about how to take create the TASK_END column if when the condition is true.

I want this data in this format.

CASE NUMBER     BX_TASK_START       BX_TASK_END           FH_TASK_START       FH_TASK_END
09232           11/8/2019 14:40     11/8/2019 15:40       11/8/2019 13:35     11/8/2019 15:12
09234           11/6/2019 11:23     11/6/2019 13:44       11/6/2019 12:54     11/6/2019 14:54

Consider handling issues of data display in application code — Strawberry, Dec 24 '19 at 09:06
@Strawberry updated the question. Actually query is requested from us, not code. — Muhammad Danish Khan, Dec 24 '19 at 09:08
@Strawberry I'm confused with THEN part. how to move two columns based on another unique column — Muhammad Danish Khan, Dec 24 '19 at 09:10

forpas · Accepted Answer · 2019-12-24T09:12:18.567

You must aggregate on these case expressions:

SELECT c.CASE_NUMBER,
   MAX(CASE WHEN mt.MAIN_TEST_NAME='BX' THEN t.TASK_START END) as 'BX_TASK_START',
   MAX(CASE WHEN mt.MAIN_TEST_NAME='BX' THEN t.TASK_END END) as 'BX_TASK_END',
   MAX(CASE WHEN mt.MAIN_TEST_NAME='FH' THEN t.TASK_START END) as 'FH_TASK_START',
   MAX(CASE WHEN mt.MAIN_TEST_NAME='FH' THEN t.TASK_END END) as 'FH_TASK_END'
FROM `CASE` c    
INNER JOIN MAIN_TEST mt ON c.CASE_ID=mt.CASE_ID
INNER JOIN JOB j on c.CASE_ID = j.JOB_ID
INNER JOIN TASK t on j.TASK_ID= t.TASK_ID
WHERE  (c.CREATION_DATE BETWEEN '2019-01-01 00:00:00' AND CURDATE())
GROUP BY c.CASE_NUMBER

How to convert multiple rows into multiple columns based on value

1 Answers1