Row to Column with Custom Value SQL

Question

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.

First parse `oMessage1` into two columns then pivot the result. — Serg, Jan 27 '22 at 07:15
If the list of Fxxxx is not fixed use a dynamic sql. What is your problem exactly, where are you stuck at? — Serg, Jan 27 '22 at 07:39
@Serg, I want to have a dynamic column. Because the 2 characters in the beginning will be more and more. F1 - F100... Don't know. — Haminteu, Jan 27 '22 at 07:45
Does this answer your question? [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Serg, Jan 27 '22 at 07:47

Pham X. Bach · Accepted Answer · 2022-01-27T07:49:17.217

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,

@Haminteu then you just change `tmp` to your table name. Or do you mean that you have **F1** to **Fn** and **n** is a big integer, or **n** is not known (maybe change dynamically in future)? — Pham X. Bach, Jan 27 '22 at 07:40
Just a sign for the value (color). F = RED, N = Green. Yes, I just thinking that maybe it's possible to make it dynamic. — Haminteu, Jan 27 '22 at 07:44
@Haminteu For more than 2 colors, you could refer to my edited answer. For dynamic color, or dynamic **Fn**, you should search and use pivot with dynamic SQL. — Pham X. Bach, Jan 27 '22 at 07:51

score 0 · Answer 2 · answered Jan 27 '22 at 07:41

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

Row to Column with Custom Value SQL

2 Answers2