January 29, 2009 at 5:08 am
declare @sql varchar(500),@max int
declare @ss nvarchar(max)
declare @cnt int
declare @acct_id ut_synthetic_key
declare @evt_stub ut_stub,@lstr varchar(800)
declare @startdate datetime
declare @enddate datetime
--set identity_insert on
set @acct_id=2000122
set @evt_stub='d15650dc-bf6a-49bc-a7a0-5e5f9131dc00'
set @startdate=getdate()-360
set @enddate=getdate()
create table #quest ( qstn_id int identity , acct_id int, evt_stub ut_stub, reg_opt_stub ut_stub, qstn_text nvarchar(300))
insert into #quest ( acct_id, evt_stub, reg_opt_stub, qstn_text)
select top 100 eq.acct_id,eq.evt_stub, eqr.reg_opt_stub,eq.qstn_text from dbo.[EVENT_QUESTION] eq with
(nolock)
JOIN dbo.[EVENT_QUESTION_REGISTRATION_OPTION] eqr with (nolock) on eqr.acct_id = eq.acct_id and eqr.evt_stub = eq.evt_stub and eqr.qstn_stub=eq.qstn_stub
set @sql = ''
select @max-2 = MAx(qstn_id) from #quest
SEt @cnt = '1'
select @sql = qstn_id from #quest where qstn_id = @cnt
While @cnt < = @max-2
begin
--print @cnt
SET @sql = @sql + ',' + cast(@cnt as varchar)
Set @cnt = @cnt + 1
end
print @sql
set @lstr = 'SELECT *
FROM
(
SELECT ess.qstn_id,ess.acct_id, ess.evt_stub, ess.reg_opt_stub, ess.qstn_text, a.answ_text FROM
#quest as ess
left JOIN dbo.[EVENT_ANSWER_DETAIL] a with (nolock) on a.acct_id = ess.acct_id and ess.qstn_stub = a.qstn_stub
and ess.reg_opt_stub = a.reg_opt_stub
and a.entity_type_id = 10
where ess.acct_id = @acct_id and ess.evt_stub = @evt_stub and ess.survey_type_id=9
) c
PIVOT
(
MAX(qstn_text)
for qstn_id in ( ' + @sql + ' ) AS pc'
print @lstr
exec(@lstr)
--drop table #quest
i m getting ERROR
"'Must declare @aact_id variable "
Please help
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 29, 2009 at 5:15 am
When you EXEC @lstr, it's executing in a different scope and the variables created in the body of the batch are not available to that scope.
Consider using sp_executesql, which supports working with variables as parameters, or alternatively resolve the variables within the string:
where ess.acct_id = ' + @acct_id + ' and ess.evt_stub = ' + @evt_stub + ' and
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 29, 2009 at 5:27 am
Thanks
but now i m geeting below error
"
Msg 206, Level 16, State 2, Line 39
Operand type clash: uniqueidentifier is incompatible with int
"
actually
[reg_opt_stub ], [qstn_stub], [evt_stub]
all above columns are of UNIQUEIDENTIFIER datatype
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 29, 2009 at 5:33 am
You have a statement in there for printing the SQL string
print @lstr
Have a look at the statement it generates. You will see that there are quotes missing.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 29, 2009 at 6:00 am
Thanks !!!!!!!!
its working fine
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply