SQL Server - Setting Multiple Values in a Case Statement?

I have a SQL Select statement where I need to return certain values depending on a condition. I need to return multiple values each time, but my understanding of the Case statement is that you can only return a single value for each case.

I'm getting around this by using UNION statements at the moment, but it all looks a bit cumbersome - is there a better way to do this? Basically, I have a bunch of prompts, each with a response of either "Yes", "No" or "In Future" (I actually have more responses, but I'll just use 3 for the example to keep it short!) - I need to produce a column for each response type, with a 1 as the value for the appropriate response, and a 0 for all others. It's probably clearer to understand if you look at the SQL...

My (simplified) query looks like this:

SELECT branch,
       promptType,
       respons开发者_StackOverflow中文版e,
       1 AS 'Yes',
       0 AS 'No',
       0 AS 'Not Discussed'
FROM prompts
WHERE response = 'Y'

UNION

SELECT branch,
       promptType,
       response,
       0 AS 'Yes',
       1 AS 'No',
       0 AS 'Not Discussed'
FROM prompts
WHERE response = 'N'

UNION

SELECT branch,
       promptType,
       response,
       0 AS 'Yes',
       0 AS 'No',
       1 AS 'Not Discussed'
FROM prompts
WHERE response = 'D'

Something like...

SELECT branch,
       prompttype,
       CASE WHEN response = 'Y' THEN 'Yes'
            WHEN response = 'N' THEN 'No'
            WHEN response = 'D' THEN 'Not Discussed'
    FROM prompts;

might be what you are after.

After your comment, perhaps...

SELECT branch,
       prompttype,
       CASE WHEN response = 'Y' THEN 1 ELSE 0 AS Yes,
       CASE WHEN response = 'N' THEN 1 ELSE 0 AS No,
       CASE WHEN response = 'D' THEN 1 ELSE 0 AS Not_Discussed
    FROM prompts;

might do it.

Have you considered creating a decoding table for the responses, and joining to that?

For example, this would create a table for decoding the responses:

CREATE TABLE decoder (response CHAR(1), [Yes] BIT, [No] BIT, [Not Discussed] BIT)
INSERT INTO decoder VALUES ('Y', 1, 0, 0)
INSERT INTO decoder VALUES ('N', 0, 1, 0)
INSERT INTO decoder VALUES ('D', 0, 0, 1)

...and then you could join to it to get similar (the same?) results as you're getting with your UNION:

SELECT
    prompts.branch,
    prompts.prompttype,
    prompts.response,
    decoder.yes,
    decoder.no,
    decoder.[Not Discussed]
FROM 
    prompts INNER JOIN decoder ON prompts.response = decoder.response

Might be an approach worth considering; it's a more relational solution than your union, and probably easier to maintain.

You need to add grouping, I think. I did this with quarterly projections. If you have a unique ID for each response, it would be something like this (otherwise it will pick max for the whole branch/prompttype/response):

SELECT uniqueID,
   branch,
   prompttype,
   response,
   MAX(CASE WHEN response = 'Y' THEN 1 ELSE 0 END) AS Yes,
   MAX(CASE WHEN response = 'N' THEN 1 ELSE 0 END) AS [No],
   MAX(CASE WHEN response = 'D' THEN 1 ELSE 0 END) AS  Not_Discussed
FROM prompts

GROUP BY uniqueID,
    branch,
    prompttype,
    response;

SELECT branch,
       prompttype,
    response,
       CASE WHEN response = 'Y' THEN 1 ELSE 0 END AS Yes,
       CASE WHEN response = 'N' THEN 1 ELSE 0 END AS [No],
       CASE WHEN response = 'D' THEN 1 ELSE 0 END AS  Not_Discussed
    FROM prompts;

If this proposed CASE statement of yours returned multiple values in a single column, what would the data type be: an array, a list, a table, an XML document, a business object, etc? For a truly relational database management system (TRDBMS), the answer would be a relation variable. But we are talking about SQL Server, here.

I think the answer you are looking for is that SQL Server's data types are scalar, not multivalued.