June 4, 2012 at 6:00 am
Dear all,
I'm getting an error as given below while executing the query:
Msg 512, Level 16, State 1, Line 4
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Given below is the sql query:
USE [RMSDB]
declare @columns varchar(max)
declare @convert varchar(max)
select @columns = stuff(( select distinct'],[' + (Select CAST(WeekStartDate AS varchar(max))AS Week
From Weeks inner join UserProductivityMapping ON Weeks.WeekId = UserProductivityMapping.WeekId)
from UserProductivityMapping for xml path('')), 1, 2,'') + ']'
--select @columns columns
set @convert =
'select * from (select * from UserProductivityMapping) WeekId
pivot(sum(PercentageProductivity) for WeekId
in ('+@columns+')) as pivottable'
execute (@convert)
But If I code as:
USE [RMSDB]
declare @columns varchar(max) --varchar(max)
declare @convert varchar(max)
select @columns = stuff(( select distinct'],[' + convert(varchar(max),WeekId)
from UserProductivityMapping for xml path('')), 1, 2,'') + ']'
--select @columns columns
set @convert =
'select * from (select * from UserProductivityMapping) WeekId
pivot(sum(PercentageProductivity) for WeekId
in ('+@columns+')) as pivottable'
execute (@convert)
It works well with WeekIds pivoted as individual columns
Please guide me in solving the above query.
Thanks in advance,
Ram
June 4, 2012 at 9:03 am
Unlike some MS error messages, this error is fairly self-explanatory. One of your subqueries is returning multiple values and that is not allowed. Specifically, the following subquery is producing all possible values for WeekStartDate:
Select CAST(WeekStartDate AS varchar(max))AS Week
From Weeks
inner join UserProductivityMapping
ON Weeks.WeekId = UserProductivityMapping.WeekId
I suspect that you want a correlated subquery here, rather than a stand-alone subquery, but it's also possible that you want to include a FOR XML PATH in your subquery, or to do away with the subquery altogether. Without sample data, DML statements, and expected results, it's really difficult to say for sure.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 4, 2012 at 10:18 pm
Hi Drew,
Please find the attached .xlsx file for your reference contains the table structure and also the expected output which may be useful for you to understand the requirement.
Many thanks,
Ram
June 5, 2012 at 8:53 am
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply