CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2005sql-server-2008-r2pivot
Pavan Kumar
Pavan Kumar

Reputation: 463

Pivot with a table variable

I'm unable to use a pivot the data of a table variable.

Its giving following error on run-time: "Must declare the scalar variable @reportData"

I have tried as mentioned below

DECLARE @reportData TABLE
(
PERSONID NUMERIC(6,0),
personname VARCHAR(100),
bu VARCHAR(50),
timeperiod VARCHAR(100),
wfstatus VARCHAR(100)
)

I'm using the below dynamic pivot query

declare @query nvarchar(max)
set @query=N'SELECT PERSONID,PERSONNAME,BU,wfstatus,'+@datelist+'
from(
SELECT PERSONID,PERSONNAME,BU,wfstatus,timeperiod
FROM
'+@reportData+') AS SOURCETABLE
PIVOT
(group by wfstatus 
FOR timeperiod
 IN('+@datelist+')
) as pivorttable
select personid,personname,bu,timeperiod,status from pivorttable'

execute(@query);

can some one help me in this? I need to use only table variable to maintain concurrency issue.!

Upvotes: 0

Views: 2652

Answers (1)

Alex K.
Alex K.

Reputation: 175936

FROM'+@reportData attempts to add a table variable to a string, which wont work as a table variable is not a string.

Given that you presumably need to populate reportData first you could switch to an explicitly created temp table

create table #reportData 
(
PERSONID NUMERIC(6,0)
...
)

Or use a Table Type;

--run once
CREATE TYPE ReportDataType AS TABLE (
    PERSONID NUMERIC(6,0),
    personname VARCHAR(100)
)


declare @reportData ReportDataType
insert @reportData values 
    (111, 'bob'),
    (222, 'alice')

declare @query nvarchar(max) = N'select * from @T'

exec sp_executesql @query, N'@T ReportDataType readonly', @reportData

Upvotes: 1

Related Questions