SQL CASE Statement and Multiple Conditions

Question

I am writing a basic SQL query to build a table in a cloud-based reporting tool, which supports a wide range of SQL "dialects" (no server-side access to the database). I have a working knowledge of SQL and it's needed to be able to construct a basic table for specific visualizations. Specifically, I need to visualize/quantify each unique instance of a value in a comma separated list from the original table. For my example, Col4 has the values a, b, c, d in various orders, which need to be counted and visualized to show how many times each value shows up each date (Col1).

The original table looks like this:

I'm using a CASE statement so I can manipulate the labeling of the output of Col4 in the new table so its easier to visualize.

Here's a shortened example of what my query looks like:

SELECT 
    Col1, CoL2, 
    CASE 
       WHEN Col4 LIKE '%a' 
          THEN 'custom label for a' 
       WHEN Col4 LIKE '%b%' 
          THEN 'Custom Label for b' 
    END AS 'Column Label' 
FROM 
    Original_Table

The desired table (for the visualization to work should look like this):

That way, the visual will show each time "a" and "b" show up regardless of their position in the comma-separated list in Col4 in the original table (so I can show number of occurrences of "a" and "b" per month)

The problem is, this CASE statement only returns TRUE if one of the first WHEN conditions is met. For example, I'm seeing results wherein if a cell contains both "a" and "b", it will return only the first TRUE condition that's encountered and doesn't also return "b" from that same comma separated list. Conversely, if "b" is listed before "a" (the ordering is variable since the selections are coming from picklists populated by end-users), then "b" is returned "TRUE" and "a" is ignored.

Essentially, how can I tweak this query to return regardless of position and regardless if two values are in the same comma-separated list? Hope that makes sense and thanks!

For example, which output exactly do you expect when `Col3` shows `c, a, b` ? — GMB, Dec 08 '18 at 00:05
Apologies - got into the weeds writing the question and forgot the desired output. Edited the original question with this info. Thanks for your help! — wyattburp86, Dec 08 '18 at 00:21
Does your SQL engine support table-valued functions? Meaning, are you able to define your own functions that return sets? — Jonathan Wilson, Dec 08 '18 at 00:24
Not when building query tables (it supports string, mathematical, aggregate and data functions) - at least from what I understand. Thanks! — wyattburp86, Dec 08 '18 at 00:28

score 1 · Accepted Answer · answered Dec 08 '18 at 00:28

1

Since you want to return multiple rows from the same original row, you need a UNION ALL

SELECT Col1, 'Custom Label for a' AS Col4
  FROM Original_Table
 WHERE Col4 LIKE '%a%'
UNION ALL
SELECT Col1, 'Custom Label for b' AS Col4
  FROM Original_Table
 WHERE Col4 LIKE '%b%'
 ORDER BY Col1, Col4

answered Dec 08 '18 at 00:28

Andreas

154,647
11
152
247

Thanks for the answer! After reading your answer, I realized that I needed a UNION ALL under each SELECT statement. I also separated out each CASE WHEN statement into its own SELECT statement and coupled with UNION ALL, I was able to get the correct result. Cheers! – wyattburp86 Dec 08 '18 at 01:09

score 0 · Answer 2 · answered Dec 08 '18 at 00:03

I've prepared comma-delimited lists using STUFF and FOR XML PATH('') in Microsoft SQL Server for a while now.

If this exact syntax doesn't work for you, you may still find some useful info on how this works here.

DECLARE @LetterTypes TABLE (Type CHAR(10))
INSERT @LetterTypes
    VALUES
    ('Consonant'),
    ('Vowell')

DECLARE @Letters TABLE (Letter CHAR(1) NOT NULL, Type VARCHAR(10) NOT NULL)
INSERT @Letters 
    VALUES
    ('A','Vowell'),
    ('B','Consonant'),
    ('C','Consonant'),
    ('D','Consonant'),
    ('E','Vowell'),
    ('F','Consonant'),
    ('G','Consonant'),
    ('H','Consonant'),
    ('I','Vowell'),
    ('J','Consonant'),
    ('K','Consonant'),
    ('L','Consonant'),
    ('M','Consonant'),
    ('N','Consonant'),
    ('O','Vowell'),
    ('P','Consonant'),
    ('Q','Consonant'),
    ('R','Consonant'),
    ('S','Consonant'),
    ('T','Consonant'),
    ('U','Vowell'),
    ('V','Consonant'),
    ('W','Consonant'),
    ('X','Consonant'),
    ('Y','Consonant'),
    ('Z','Consonant')


SELECT
    Type,
    STUFF
    (
        (
            SELECT ', ' + Letter
            FROM @Letters L
            WHERE LT.Type = L.Type
            ORDER BY L.Letter
            FOR XML PATH('')
        ), 1, 2, ''
    ) AS Letters
FROM @LetterTypes LT

Output:

score 0 · Answer 3 · answered Dec 08 '18 at 00:12

0

I re-read your question and I think I understand better now. You wish to transform a, b, and c into custom labels, right?

You could use multiple calls to REPLACE for that. Bear with me!

SELECT 
    Col1, 
    CoL2, 
    REPLACE(
    REPLACE(
    REPLACE(Col4,
        'a', 'Custom Label for a'),
        'b', 'Custom Label for b'), 
        'c', 'Custom Label for c') as ReLabeledCol4
FROM Original_Table

answered Dec 08 '18 at 00:12

Jonathan Wilson

4,138
1
24
36

Apologies - got into the weeds writing the question and forgot the desired output. Edited the original question with this info. Thanks for your help! – wyattburp86 Dec 08 '18 at 00:21

SQL CASE Statement and Multiple Conditions

3 Answers3