'Row to Column with Custom Value SQL

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.

sqltsqlsql-server-2012


Solution 1:[1]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 RamMohan222

Related Questions