代码之家 › 专栏 › 技术社区 › TabbyCool

SQL Server-在Case语句中设置多个值?

case select sql-server sql

TabbyCool · 技术社区 · 14 年前

目前我正在使用UNION语句来解决这个问题,但这看起来有点麻烦-有更好的方法吗?基本上,我有一堆提示,每一个都有一个“是”、“否”或“将来”的响应(实际上我有更多的响应,但我将使用3作为示例以保持简短!)我需要为每个响应类型生成一列,相应响应的值为1,其他响应的值为0。如果你看一下SQL,可能会更清楚地理解。。。

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'

5 回复 | 直到 14 年前

Matt Gibson 14 年前

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)

……然后你可以加入它,得到与你的工会类似的(相同的?)结果:

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

可能是一个值得考虑的方法;这是一个比你的工会关系更密切的解决方案,而且可能更容易维护。

Brian Hooper 14 年前

有点像。。。

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

可能就是你想要的。

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;

也许可以。

Dan 13 年前

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;

Kashif 14 年前

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;

onedaywhen 14 年前

如果这个提议 CASE

我认为您需要的答案是SQLServer的数据类型是标量的,而不是多值的。