CODEWITHSUNDEEP
sql
RShoggoth
RShoggoth

Reputation: 31

SQL - Conditional dummy column based off value set

Table Name : SimpleTable

| A | B | C | D | E | F | 
+----------------------+ 
| 5 | 3 | 5 | 9 | 6 | 3 | 
| 7 | 5 | 0 | 0 | 9 | 2 | 
| 1 | 6 | 1 | 2 | 4 | 5 | 
| 2 | 9 | 4 | 4 | 9 | 0 | 
| 9 | 2 | 3 | 5 | 2 | 8 |

I have a simple table that looks like this, what I essentially want to do is do a select on this simple table but in the results have an additional dummy column (Called "Dummy" for simplicity). This dummy column is built using the values from the the other columns, if the integer is 7 or over it contains the column's title. For example the first row's dummy would display "D" but the 2nd row would display "A/E". I've not been able to figure out how this logic is achieved in SQL but would greatly appreciate any help.

Upvotes: 1

Views: 1787

Answers (2)

Nikola Markovinović
Nikola Markovinović

Reputation: 19356

If this is Sql Server, then

select *,
       stuff (case when a >= 7 then '/A' else '' end
            + case when b >= 7 then '/B' else '' end
            + case when c >= 7 then '/C' else '' end
            + case when d >= 7 then '/D' else '' end
            + case when e >= 7 then '/E' else '' end
            + case when f >= 7 then '/F' else '' end,
         1, 1, '') dummy
  from SimpleTable

In case that not one column is greater or equal to 7 dummy will be null.

Upvotes: 3

George D
George D

Reputation: 2365

SELECT A, B, C, D, E, F,
CASE 
 WHEN A >= 7 THEN 'A/' 
 ELSE '' END
+
 CASE 
 WHEN B >= 7 THEN 'B/' 
 ELSE '' END
+
CASE 
 WHEN C >= 7 THEN 'C/' 
 ELSE '' END
+
CASE 
 WHEN D >= 7 THEN 'D/' 
 ELSE '' END
+
CASE 
 WHEN E >= 7 THEN 'E/' 
 ELSE '' END
+
CASE 
 WHEN F >= 7 THEN 'F' 
 ELSE '' END

AS Dummy
FROM SimpleTable

Upvotes: 0

Related Questions