Geek asks and answers

SQL query: create a category column based on a varchar column in a table containing specific values

I have a table similar to the following:

Date        Description     Value1  Value2  
01/01/2012  shiny colour    2       0       
01/01/2012  yellow colour   2       2       
03/01/2012  matt colour     2       2       
03/01/2012  matt            4       1   
03/01/2012  shiny           2       2

I want to write a SELECT SQL (T-SQL) query that will display all the above columns and also display an additional column as the output of a SELECT statement whose value depends on the presence of the word “color” in the Description (if “color” is present, this will be one value if it did not show another value).

(I would also like to display another additional column on top of the value, the meaning of which depends on the presence of the words “matte” or “shiny” in the “Description” column. But I assume that the method for this will be similar).

I believe I should do this using the COALESCE function, but am I not familiar with this and trying my best to get something to work?

EXTENSION

Hey, thanks for your answers. They are really helpful. I have another question extension. My second generated column relies on the information in the first created column. So something like:

SELECT *,
CASE 
    WHEN Description LIKE '%colour%' THEN 'SomeValue'
    ELSE 'Unclassified'
END AS Category1,
CASE
    WHEN AnotherColumn LIKE 'Something' THEN 'SomeValue'
    WHEN Category1='Unclassified' THEN 'Unclassified'
    ELSE 'Generic'
END AS Category2
FROM table_name

How can I get category 2 output for output on category1 output? I am trying something like the above, but it does not work.

My add question was answered: The T-SQL CASE statement relies on another CASE statement in the same SELECT query

+5
source share
2 answers
SELECT *,
       CASE WHEN Description LIKE '%colour%' THEN
            1
       ELSE
            0
       END AS HasColour,
       CASE WHEN Description LIKE '%matt%' THEN
            1
       ELSE
            0
       END AS HasMatt,
       CASE WHEN Description LIKE '%shiny%' THEN
            1
       ELSE
            0
       END AS HasShiny
FROM   table_name

, . , , , , .

+2

, , case:

SELECT Date,  
Description, 
Value1, 
Value2, 
Case when Description like '%colour%' then OTHERCOL else OTHERCOL2 end as Colourful,
Case when Description like '%matt%' then OTHERCOL else OTHERCOL2 end as Matt,
Case when Description like '%shiny%' then OTHERCOL else OTHERCOL2 end as Shiny, 
FROM yourTable
0

More articles:


All Articles