Row to Column with Custom Value SQL-CodePudding

I have the following table:

oSerial     oDateTime               oMessage1  
--------------------------------------------------
123456      2022-01-01 08:00:00     F1F           
123456      2022-01-01 08:10:00     F2F   
123456      2022-01-01 08:45:00     F5F
123456      2022-01-01 09:05:00     F1N

I want to have the following result:

oSerial     oDateTime              F1       F2     F3     F4     F5
--------------------------------------------------------------------
123456      2022-01-01 08:00:00    RED      
123456      2022-01-01 08:10:00             RED
123456      2022-01-01 08:45:00                                  RED
123456      2022-01-01 09:05:00    GREEN

So, if the last character under oMessage1 is F, then the value should be RED. If the last character under oMessage1 is N, then the value should GREEN. The value will be placed under F1 - F5 column based on 2 characters in the beginning under oMessage1.

Any advice? Really appreciated.
Thank you.

CodePudding user response：

You could use this

WITH tmp AS 
(
    SELECT 123456 AS oSerial, '2022-01-01 08:00:00' AS oDateTime, 'F1F' AS oMessage1 UNION ALL           
    SELECT 123456 AS oSerial, '2022-01-01 08:10:00' AS oDateTime, 'F2F' AS oMessage1 UNION ALL   
    SELECT 123456 AS oSerial, '2022-01-01 08:45:00' AS oDateTime, 'F5F' AS oMessage1 UNION ALL
    SELECT 123456 AS oSerial, '2022-01-01 09:05:00' AS oDateTime, 'F1N' AS oMessage1 
)
SELECT oSerial, oDateTime, 
    CASE 
        WHEN oMessage1 LIKE 'F1%' AND oMessage1 LIKE '%F' THEN 'RED'
        WHEN oMessage1 LIKE 'F1%' AND oMessage1 LIKE '%N' THEN 'GREEN'
    END AS f1,
    CASE 
        WHEN oMessage1 LIKE 'F2%' AND oMessage1 LIKE '%F' THEN 'RED'
        WHEN oMessage1 LIKE 'F2%' AND oMessage1 LIKE '%N' THEN 'GREEN'
    END AS f2,
    CASE 
        WHEN oMessage1 LIKE 'F3%' AND oMessage1 LIKE '%F' THEN 'RED'
        WHEN oMessage1 LIKE 'F3%' AND oMessage1 LIKE '%N' THEN 'GREEN'
    END AS f3,
    CASE 
        WHEN oMessage1 LIKE 'F4%' AND oMessage1 LIKE '%F' THEN 'RED'
        WHEN oMessage1 LIKE 'F4%' AND oMessage1 LIKE '%N' THEN 'GREEN'
    END AS f4,
    CASE 
        WHEN oMessage1 LIKE 'F5%' AND oMessage1 LIKE '%F' THEN 'RED'
        WHEN oMessage1 LIKE 'F5%' AND oMessage1 LIKE '%N' THEN 'GREEN'
    END AS f5
FROM tmp
ORDER BY oSerial, oDateTime;

If you have more than 2 colors then you should change to this to avoid duplicate code:

CASE WHEN oMessage1 LIKE 'F1%' THEN
    CASE 
        WHEN oMessage1 LIKE '%F' THEN 'RED'
        WHEN oMessage1 LIKE '%N' THEN 'GREEN'
        WHEN oMessage1 LIKE '%O' THEN 'OTHERS COLOR 1'
        --WHEN oMessage1 LIKE '%X' THEN 'OTHERS COLOR X'
    END
END AS f1,

CodePudding user response：

SELECT oSerial, oDateTime,
     CASE WHEN SUBSTR(oMessage1,1,2) = 'F1' THEN 
     CASE WHEN SUBSTR(oMessage1,3,3)='F' THEN 'RED' WHEN SUBSTR(oMessage1,3,3)='N' THEN 'GREEN' END 
     ELSE null END as F1,
     
     CASE WHEN SUBSTR(oMessage1,1,2) = 'F2' THEN 
     CASE WHEN SUBSTR(oMessage1,3,3)='F' THEN 'RED' WHEN SUBSTR(oMessage1,3,3)='N' THEN 'GREEN' END 
     ELSE null END as F2,
     
     CASE WHEN SUBSTR(oMessage1,1,2) = 'F3' THEN 
     CASE WHEN SUBSTR(oMessage1,3,3)='F' THEN 'RED' WHEN SUBSTR(oMessage1,3,3)='N' THEN 'GREEN' END 
     ELSE null END as F3,
     
     CASE WHEN SUBSTR(oMessage1,1,2) = 'F4' THEN 
     CASE WHEN SUBSTR(oMessage1,3,3)='F' THEN 'RED' WHEN SUBSTR(oMessage1,3,3)='N' THEN 'GREEN' END 
     ELSE null END as F4,
     
     CASE WHEN SUBSTR(oMessage1,1,2) = 'F5' THEN 
     CASE WHEN SUBSTR(oMessage1,3,3)='F' THEN 'RED' WHEN SUBSTR(oMessage1,3,3)='N' THEN 'GREEN' END 
     ELSE null END as F5
          
          FROM TableName