Finding a value and giving out some infos

Question

coming back with a new question concerning SQL (Server 2016 or above).

I have a data view with 5 fields like:

ID_Card | ID_Name| ID_Details       | ID_Date   |ID_Validation
12345     Joe      C30,C40,C50,C60    20.11.20   YES

Now I want to give out some information for ID_Details. If C30 is in the ID_Details, I want to give out some text like "Further information needed", for C40 for example I want to give out "No updates present". and so on. The Details list is limited to 5 entrys, which must not be present. The list is separated in the ID_Details field with an comma.

Thanks in advance!

Can you change the design of this? It a huge anti-pattern/source of problems to have multiple bits of data in a single column. — Alex K., Nov 20 '20 at 15:00
Seems like the real problem here is your design. Don't store delimited data in your RDBMS. — Thom A, Nov 20 '20 at 15:00
This will answer your question: [Turning a Comma Separated string into individual rows](https://stackoverflow.com/q/5493510/2029983) — Thom A, Nov 20 '20 at 15:02
Also, your `ID_Date` column having a value `20.11.20 ` smells like you're storing your date as a `varchar`, and with a 2 character year; that's also a *really* bad idea. — Thom A, Nov 20 '20 at 15:03
It's not my database layout @Larnu I cannot change the database layout. It's very old and really disgusting, that's true. But in fact, we have these data given and i need to deal with it — Christian, Nov 20 '20 at 15:06
*"I cannot change the database layout."* Then escalate it to whomever can, storing dates as string, and delimited data in a column are breaking some of the most fundamental rules for data in an RDBMS. — Thom A, Nov 20 '20 at 15:10
I know, this is really not the thought of normalization in a database, but I need to deal with the given situation. Thanks for you answer so far, but how can I combine multiple values, in this case all the six information in one text? — Christian, Nov 20 '20 at 15:24

score 0 · Answer 1 · answered Nov 20 '20 at 15:09

Below is an example using a CASE expression with a STRING_SPLIT subquery. A better solution would be database design in (at least) first normal form.

SELECT
     ID_Card
    ,ID_Name
    ,ID_Details
    ,ID_Date
    ,ID_Validation
    , CASE WHEN EXISTS( 
        SELECT 1
        WHERE 'C40' IN(
            SELECT value
            FROM STRING_SPLIT(ID_DETAILS,',')))
        THEN 'Further information needed' END AS SomeText
FROM dbo.Example;

Finding a value and giving out some infos

1 Answers1