Syntax error with pivot table

Question

Syntax error with pivot table

azlonghorn

Old Hand

Points: 313
More actions
October 21, 2014 at 12:04 pm

#297679

I cannot seem to figure out where this error is. I am getting "Incorrect syntax near '('." It seems to be something in the pivot clause, but I can't figure it out. Thanks
Here is the code...
CREATE TABLE #T1 (
Territory_NM VARCHAR(100),
Product_Type VARCHAR(100),
ReportCount INT
)
--reports by region
INSERT INTO #t1
selectTerritory_NM, Product_Type, COUNT(Order_Product_ID) AS ReportCount
From[dbo].[DeliveredReportsSummary]
WHEREdeliveredDate >= '01/01/2014' AND deliveredDate < '11/01/2014'
Group BY Territory_NM, Product_Type
order by Territory_NM, Product_Type
select Territory_NM
,isnull([Litigation Premier],0) as [Litigation Premier]
,isnull([Litigation Gold],0) as [Litigation Gold]
,isnull([Patent Gold],0) as [Patent Gold]
,isnull([Patent Silver],0) as [Patent Silver]
,isnull([Expert Publications],0) as [Expert Publications]
,isnull([Expert News and Internet Activity],0) as [Expert News and Internet Activity]
from #T1
pivot (sum(ReportCount)
for Product_Type IN ([Litigation Premier],[Litigation Gold],[Patent Gold],[Patent Silver],[Expert Publications],[Expert News and Internet Activity]))
as productsByRegion

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Your pivot statement is correct, your syntax error might be elsewhere.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

azlonghorn Old Hand Points: 313 More actions · Answer 2

Thanks Luis. I can't figure out where the syntax might be. Any further assistance?

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Do you have something else in your code? Are you missing an end?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

azlonghorn Old Hand Points: 313 More actions · Answer 4

only line I left out was "drop table #t1" at the end. Thanks

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

Do you have a BEGIN with no END?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

azlonghorn Old Hand Points: 313 More actions · Answer 6

azlonghorn

Old Hand

Points: 313

October 21, 2014 at 1:43 pm

#1753860

I have neither in this block of code. WYSIWYG