Problem with case statement
I have the following Query:
SELECT CASE WHEN [Question Order] IN ( 6, 11 )
THEN CASE WHEN [Question Part Label] = 'Other (Please specify):'
THEN [Answer Text]
ELSE [Question Part Label]
END
ELSE 'replace code here'
END,[Respondent ID]
FROM Results
WHERE [Question Order] IN ( 6, 11 ) AND [Answer Label] = 'Yes'
Now I want to replace this code where it says 'replace code here'
select
stuff((select ','+T2.[Question Part Label] from Results as T2
where T1.[Respondent ID] = T2.[Respondent ID] for xml path(''), type).value('.', 'varchar(max)'), 1, 1, '')
as Label from Results as T1 group by T1.[Respondent ID]
When I do that I get the following error
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Sample Data:
Respondent ID [Question Order] [Question Part Label] [Answer Text] [Answer Label]
124587 6 It was not clear NULL Yes
124587 6 Did not Undersstand NULL Yes
124589 6 Other (Please specify): Not enough Yes
125654 6 Too Fast NULL Yes
124582 11 Not frequent NULL Yes
The output Shpuld be:
Respondent ID [Question Part Label]
124587 It was not clear,Did not Undersstand
124589 Not Enough
125654 Too Fast
124582 Not frequent
The logic is whenever Question Order is 6 or 11 then I need to dislay the [Question Part Label] if the [Question Part Label] has multiple value for one Respondent_ID then I need to concatenate them but when the value of [Question Part Label] is Other (Please specify): then I need to use value from Answer Text column an
How can I fix this?
DECLARE @Results TABLE
(
Respondent_ID INT,
[Question Order] INT,
[Question Part Label] VARCHAR(40),
[Answer text] VARCHAR(80),
[Answer Label] VARCHAR(10)
);
INSERT @Results
SELECT 124587, 6, 'It was not clear', NULL, 'Yes'
UNION SELECT 124587, 6, 'Did not Undersstand', NULL, 'Yes'
UNION SELECT 124589, 6, 'Other (Please specify):', 'Not enough', 'Yes'
UNION SELECT 124654, 6, 'Too Fast', NULL, 'Yes'
UNION SELECT 124582, 11, 'Not frequent', NULL, 'Yes';
WITH x AS
(
SELECT Respondent_ID
FROM @Results
WHERE [Question Order] IN (6,11)
GROUP BY Respondent_ID
)
SELECT x.Respondent_ID, Label = STUFF((SELECT ',' + CASE
WHEN [Question Part Label] = 'Other (Please specify):' THEN [Answer text]
ELSE [Question Part Label] END
FROM @Results
WHERE [Question Order] IN (6,11)
AND Respondent_ID = x.Respondent_ID
FOR XML PATH(''), TYPE).value(N'./text()[1]', N'varchar(max)'), 1, 1, '')
FROM x;
Add this where it says 'replace code here'
stuff((select ','+T2.[Question Part Label]
from Results as T2
where T1.[Respondent ID] = T2.[Respondent ID]
for xml path(''), type).value('.', 'varchar(max)'), 1, 1, '')
And add T1 as an alias to your result
table in your main query.