SQL Server-在Case语句中设置多个值? [英] SQL Server - Setting Multiple Values in a Case Statement?

问题描述

我有一个SQL Select语句，在该语句中我需要根据条件返回某些值.我每次都需要返回多个值，但是我对Case语句的理解是，对于每种情况，您只能返回一个值.

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.

我现在正在通过使用UNION语句来解决这个问题，但是这看起来有点麻烦-是否有更好的方法来做到这一点?基本上，我有一堆提示，每个提示的响应都为是"，否"或将来"(我实际上有更多的响应，但是我只使用3个示例来使它简短！) -我需要为每种响应类型生成一列，其中的1代表相应响应的值，0代表所有其他响应的值.如果您看一下SQL，可能更容易理解.

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,
       response,
       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'

推荐答案

您是否考虑过为响应创建一个解码表，并加入该表?

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)

...然后您可以加入其中以得到与UNION相似的结果(相同?):

...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.

这篇关于SQL Server-在Case语句中设置多个值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！