SQL - 复杂存储过程需要在计数后将'case when'的值从整数更改为 VarChar

Question

我有一个NFL游泳池的网站。我能够显示用户选择和用户拥有的胜利数量。截至目前，我的 CASE WHEN 使用整数作为赢或输。我想将其更改为 1 = "赢"和 0 = "输"，但仍然计算用户的获胜数量。

这可能吗？

感谢您的任何帮助。

我的代码：

DECLARE @commondata TABLE (FullName VARCHAR(30), Game_1 VARCHAR(30), Game_2 VARCHAR(30), Game_3 VARCHAR(30), Game_4 VARCHAR(30), Game_5 VARCHAR(30),
GameResults_1 INT, GameResults_2 INT, GameResults_3 INT, GameResults_4 INT, GameResults_5 INT)
INSERT INTO @commondata (FullName, Game_1, Game_2, Game_3, Game_4, Game_5, 
GameResults_1, GameResults_2 , GameResults_3, GameResults_4, GameResults_5)
SELECT UserPicks.FullName, UserPicks.Game_1, UserPicks.Game_2, UserPicks.Game_3, UserPicks.Game_4, UserPicks.Game_5,
(CASE WHEN UserPicks.Game_1 = WeeklyResults.GameResults_1 THEN 1 ELSE 0 END) AS GameResult_1,
(CASE WHEN UserPicks.Game_2 = WeeklyResults.GameResults_2 THEN 1 ELSE 0 END) AS GameResult_2,
(CASE WHEN UserPicks.Game_3 = WeeklyResults.GameResults_3 THEN 1 ELSE 0 END) AS GameResult_3,
(CASE WHEN UserPicks.Game_4 = WeeklyResults.GameResults_4 THEN 1 ELSE 0 END) AS GameResult_4,
(CASE WHEN UserPicks.Game_5 = WeeklyResults.GameResults_5 THEN 1 ELSE 0 END) AS GameResult_5

FROM UserPicks
JOIN WeeklyResults ON UserPicks.Week = WeeklyResults.Week
WHERE WeeklyResults.Week = 'Week1'
-- Unpivot the table.  
(SELECT FullName, Game_1, Game_2, Game_3, Game_4, Game_5, SUM(Points) As Total
FROM 
(SELECT *
FROM @commondata) pvt 
UNPIVOT  
(Points FOR Game IN   
(GameResults_1, GameResults_2, GameResults_3, GameResults_4, GameResults_5)
)AS Total 
GROUP BY FullName, Game_1, Game_2, Game_3, Game_4, Game_5)

Answer 1

试试这个，我冒昧地重新格式化，删除多余的行并使用表格首字母缩略词来使其更易于阅读/理解：

DECLARE @commondata TABLE (FullName VARCHAR(30)
, Game_1 VARCHAR(30)
, Game_2 VARCHAR(30)
, Game_3 VARCHAR(30
, Game_4 VARCHAR(30)
, Game_5 VARCHAR(30)
, GameResults_1 VARCHAR(4)
, GameResults_2 VARCHAR(4)
, GameResults_3 VARCHAR(4)
, GameResults_4 VARCHAR(4)
, GameResults_5 VARCHAR(4));
INSERT INTO @commondata
SELECT UP.FullName
, UP.Game_1
, UP.Game_2
, UP.Game_3
, UP.Game_4
, UP.Game_5
, IIF(UP.Game_1 = WR.GameResults_1, 'Won', 'Lost') AS GameResult_1
, IIF(UP.Game_2 = WR.GameResults_2, 'Won', 'Lost') AS GameResult_2
, IIF(UP.Game_3 = WR.GameResults_3, 'Won', 'Lost') AS GameResult_3
, IIF(UP.Game_4 = WR.GameResults_4, 'Won', 'Lost') AS GameResult_4
, IIF(UP.Game_5 = WR.GameResults_5, 'Won', 'Lost') AS GameResult_5
FROM UserPicks UP
JOIN WeeklyResults WR ON UP.Week = WR.Week
WHERE WR.Week = 'Week1';
-- Unpivot the table.  
SELECT FullName
, Game_1
, Game_2
, Game_3
, Game_4
, Game_5
, SUM(IIF(Result='Won', 1, 0)) As Total
FROM @commondata 
UNPIVOT (
Result FOR Game IN (GameResults_1
, GameResults_2
, GameResults_3
, GameResults_4
, GameResults_5)
) AS Total 
GROUP BY FullName
, Game_1
, Game_2
, Game_3
, Game_4
, Game_5;