Pivot basic question

Question

Pivot basic question

as_1234

SSCrazy

Points: 2864
More actions
September 16, 2017 at 10:49 am

#340058

All,
Apologises for the basic question. I have the following pivot:
DECLARE @SQLStr NVARCHAR(max)
declare @query AS NVARCHAR(MAX)
SELECT @SQLStr = COALESCE(@SQLStr + ',', '') + [a].[Column]
FROM (SELECT '''' + knownas +'''' AS [Column]
FROM standard..tabstaff where department='ccare' and active=1) AS a
set @query='select tabusrProductGroup.title,' + @SQLStr + ' from tabusrTelephoneTemp, tabusrProductGroup,
standard..tabstaff
where ProductGroupRef=tabusrProductGroup.uniqueref
and Standard..tabstaff.uniqueref=StaffID
PIVOT
(count(datetime)
for knownas IN ('+@SQLStr+')
)'
execute @query
The error I get is:
The name 'select tabusrProductGroup.title,'Name1','Name2' from tabusrTelephoneTemp, tabusrProductGroup,
standard..tabstaff
where ProductGroupRef=tabusrProductGroup.uniqueref
and Standard..tabstaff.uniqueref=StaffID
PIVOT
(count(datetime)
for knownas IN ('Name1','Name2')
)' is not a valid identifier.
Can anyone identify what the cause of the error is?
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Andrew P SSCarpal Tunnel Points: 4649 More actions · Answer 1

I believe you need to wrap your @query variable in brackets, as below. Without those, EXECUTE is looking for a stored procedure with the name of "select tabusrProductGroup.title,...".

EXECUTE (@query)
Andrew P.

as_1234 SSCrazy Points: 2864 More actions · Answer 2

Hello,

Thanks for your help.

That's resolved that problem. Now it complains of 'Incorrect syntax near the keyword 'PIVOT'.' I'm still working on trying to solve it myself I just thought I'd post this to say thanks for the help and in case others hit the same error as I originally did.

Thanks

Andrew

Andrew P SSCarpal Tunnel Points: 4649 More actions · Answer 3

Thanks as1981, I recommend trying to appropriate the examples in the Books Online article on PIVOT, first without the dynamic SQL clouding the issue.

If you could do with more assistance on this, could you please describe what you're trying to achieve, and the relationships between the tables?

as_1234 SSCrazy Points: 2864 More actions · Answer 4

Hello,

Thanks. I found the issue about 10 minutes go (by doing similar to as you suggested) but had got chance to post yet.

In case it helps anyone else the correct query is:

DECLARE @SQLStr NVARCHAR(max)

declare @query AS NVARCHAR(MAX)

SELECT @SQLStr = COALESCE(@SQLStr + ',', '') + [a].[Column]

FROM (SELECT '[' + knownas +']' AS [Column]

FROM standard..tabstaff where department='ccare' and active=1) AS a

set @query='

set dateformat dmy

select * from (select tabusrProductGroup.title,datetime,knownas from tabusrTelephoneTemp, tabusrProductGroup,

standard..tabstaff

where ProductGroupRef=tabusrProductGroup.uniqueref

and Standard..tabstaff.uniqueref=StaffID

) as src

PIVOT

(count(datetime)

for knownas IN ('+@SQLStr+')

) prv'

execute (@query)

Andrew P SSCarpal Tunnel Points: 4649 More actions · Answer 5

That's great news, thanks as1981.

If I may be a little bold, I suggest being careful to include the table name when referencing a column (tabusrTelephoneTemp.StaffID instead of just StaffID) and avoiding old-style joins to make it easier for anyone who works with or inherits the code from you to understand the logic.

Andrew P.

as_1234 SSCrazy Points: 2864 More actions · Answer 6

Hello,

Sorry for my slow reply.

Both valid points. I tend to be lazy when I'm doing test queries and didn't think to correct it before posting, I'll remember for next time.

Thanks