CODEWITHSUNDEEP
mysqlsql
Fadly Dzil
Fadly Dzil

Reputation: 2206

Columns to rows with CASE EXPRESSION

I have a lot of record in table in mysql database, so I just take two records as example. Here it is :

SELECT a.REPAIR_ESTIMATE_ID, a.EIR_REF, 
       a.PRE_APPROVED_DATE, a.FIRST_APPROVED_DATE, a.SECOND_APPROVED_DATE 
FROM tb_master_repair_estimate a
WHERE a.REPAIR_ESTIMATE_ID IN (21,23)

I get this :

+--------------------+---------+-------------------+---------------------+----------------------+
| REPAIR_ESTIMATE_ID | EIR_REF | PRE_APPROVED_DATE | FIRST_APPROVED_DATE | SECOND_APPROVED_DATE |
+--------------------+---------+-------------------+---------------------+----------------------+
|                 21 | B6790   | 2016-12-15        | 2016-12-16          | NULL                 |
|                 23 | J6791   | 2016-12-10        | 2016-12-11          | 2016-12-13           |
+--------------------+---------+-------------------+---------------------+----------------------+
2 rows in set

My goal is, I want to indexing (in my mind) in column PRE_APPROVED_DATE, FIRST_APPROVED_DATE,SECOND_APPROVED_DATE. It should be like this :

PRE-> A
FIRST -> B
SECOND -> C

So, I Create a query like this :

SET @pre = "A";
SET @first = "B";
SET @two = "C";

SELECT a.REPAIR_ESTIMATE_ID, a.EIR_REF,
    CASE 
        WHEN a.PRE_APPROVED_DATE IS NOT NULL THEN @pre
        WHEN a.FIRST_APPROVED_DATE IS NOT NULL THEN @first
        WHEN a.SECOND_APPROVED_DATE IS NOT NULL THEN @two
        ELSE 0
    END AS `LEVEL`  
    FROM tb_master_repair_estimate a

    WHERE a.REPAIR_ESTIMATE_ID IN (21,23)

But I get like this :

+--------------------+---------+-------+
| REPAIR_ESTIMATE_ID | EIR_REF | LEVEL |
+--------------------+---------+-------+
|                 21 | B6790   | A     |
|                 23 | J6791   | A     |
+--------------------+---------+-------+
2 rows in set

I need like this

+--------------------+---------+-------+
| REPAIR_ESTIMATE_ID | EIR_REF | LEVEL |
+--------------------+---------+-------+
|                 21 | B6790   | A     |
|                 21 | B6790   | B     |
|                 23 | J6791   | A     |
|                 23 | J6791   | B     |
|                 23 | J6791   | C     |
+--------------------+---------+-------+

Please Advise... Any help it so appreciated.

Upvotes: 1

Views: 32

Answers (2)

tran.thang
tran.thang

Reputation: 1

> YOU CAN TRY

SELECT a.REPAIR_ESTIMATE_ID, a.EIR_REF,
    CASE 
        WHEN a.PRE_APPROVED_DATE IS NOT NULL THEN 'A'     
        ELSE '0'
    END AS 'LEVEL'  
    FROM tb_master_repair_estimate a
union all

SELECT a.REPAIR_ESTIMATE_ID, a.EIR_REF,
    CASE 
    WHEN a.FIRST_APPROVED_DATE IS NOT NULL THEN 'B'
        ELSE '0'
    END AS 'LEVEL'  
    FROM tb_master_repair_estimate a
union all

SELECT a.REPAIR_ESTIMATE_ID, a.EIR_REF,
    CASE 
    WHEN a.SECOND_APPROVED_DATE IS NOT NULL THEN 'C'
    ELSE '0'
    END AS 'LEVEL'  
    FROM tb_master_repair_estimate a

Upvotes: 0

sagi
sagi

Reputation: 40481

No need for case expression, use UNION ALL :

SELECT a.REPAIR_ESTIMATE_ID, a.EIR_REF, @pre as level
FROM tb_master_repair_estimate  a
WHERE a.PRE_APPROVED_DATE IS NOT NULL -- "A"
UNION ALL
SELECT a.REPAIR_ESTIMATE_ID, a.EIR_REF, @first as level
FROM tb_master_repair_estimate  a
WHERE a.FIRST_APPROVED_DATE IS NOT NULL -- "B"
UNION ALL
SELECT a.REPAIR_ESTIMATE_ID, a.EIR_REF, @second as level
FROM tb_master_repair_estimate  a
WHERE a.SECOND_APPROVED_DATE IS NOT NULL -- "C"

If you want it for those specific IDs then add the filter .

Upvotes: 1

Related Questions