Can SQL Case statement be used to select a database column

Question

I have this SQL:

SELECT sets.set_id, responses.Question_Id, replies.reply_text, responses.Response
FROM response_sets AS sets
INNER JOIN responses AS responses ON sets.set_id = responses.set_id
INNER JOIN replies AS replies ON replies.reply_id = responses.reply_id
WHERE sets.Sctn_Id_Code IN ('668283', '664524')

A partial result:

enter image description here

I want to replace the reply_text and response columns with one column that will have the value of the response if it isn't null and the reply text value otherwise. I'm not sure if this can be done with a case statement, at least nothing I've dug up leads me to think that I can. Am I incorrect in assuming this?

Replace this: `replies.reply_text, responses.Response` with `COALESCE(responses.Response, replies.reply_text) AS AliasName` — Dave Mason, Mar 11 '14 at 18:34
`sets` is a future sQL Server is a reserved word, so it is not a good name for an identifier. — Gordon Linoff, Mar 11 '14 at 18:38
Duly noted and changed Gordon. Thanks DMason and Jeff for the answer. — Michael Robinson, Mar 11 '14 at 18:48

score 0 · Accepted Answer · answered Mar 11 '14 at 18:35

You could use a case statement, but COALESCE would be easier:

Select sets.set_id, responses.Question_Id, COALESCE(responses.Response, replies.reply_text)
From response_sets as sets
inner join responses as responses on sets.set_id = responses.set_id
inner join replies as replies on replies.reply_id = responses.reply_id
where sets.Sctn_Id_Code in ('668283', '664524')

COALESCE allows you to list two or more expressions, and takes the first one that isn't null.

score 0 · Answer 2 · edited May 23 '17 at 12:21

0

Yes, ISNULL(responses.Response, replies.reply_text) or COALESCE also works. Please see this post for more information and examples. This article dives deeper into ISNULL vs COALESCE.

edited May 23 '17 at 12:21

Community

1
1

answered Mar 11 '14 at 18:36

SQLMason

3,275
1
30
40

score 0 · Answer 3 · answered Mar 11 '14 at 18:38

This is from an Oracle select statement, but if it works in Oracle, it's possible it will also work in SQL Server (with some syntax changes):

, (case when bseg1.start_dt is null then bseg2.start_dt else bseg1.start_dt end) bseg_startdt

This is selecting the second start date when the first is null.

Specific SQL Server answers will be a better option, of course.

Can SQL Case statement be used to select a database column

3 Answers3