I have a table like this
ID | Type | Val0 | Val1
1 | 0 | A | NULL
2 | 1 | NULL | B
I need to select Val0 when the type is 0, and Val1 when the type is 1, and ValN when type is N...
How can I do that?
4 Answers
SELECT CASE
WHEN Type = 0 THEN Val0
WHEN Type = 1 Then Val1
.
.
WHEN Type = N Then ValN
END
FROM tbl
1 Comment
eidylon
This apparently does have a problem though if you're using a table from another database, because apparently Sql Server then imposes a maximum number of cases of 10... which is pretty small.
The way I read this, you need to use UNION:
SELECT a.val0
FROM TABLE a
WHERE a.type = 0
UNION ALL
SELECT a.val1
FROM TABLE a
WHERE a.type = 1
UNION ALL ...
UNION ALL doesn't remove duplicates, and is faster than UNION (because it removes duplicates).
Doing this dynamically is possible.
2 Comments
dcp
I interpreted it to be "give me a single value back based on the given type". But you may be totally right. It was good to ask for an expected output :).
OMG Ponies
@dcp: I've been bitten too many times my incorrect assumptions - I ask & wait while watching to see if it made sense to anyone else
For low values of N, you can do it ad-hoc using the CASE statement, like CASE Type WHEN 0 THEN Val0 WHEN 1 THEN Val1 END. If your N is bigger, you should probably normalize your database (i.e. put ID => ValN mappings in a different table).
1 Comment
eidylon
The CASE statement works well. The normalization suggestion is good, but not always possible, when you are integrating with someone elses database. Unfortunately.
See CASE statement http://msdn.microsoft.com/en-us/library/ms181765.aspx
SELECT ... WHERE ID = 1 -- A