tsql Dynamic Pivot Table returning "Incorrect syntax near the keyword 'AS'

Trying to build a Dynamic Pivot table to run daily and email a group.
I have the SQL statement that I got from MS and tweaked it for my data, but U am getting this syntax error that I do not see, but if I remark out the Pivot part of the statement if runs.

Here is the error:
Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'AS'.

Below is the statements I am running.

DECLARE @PivotColumnHeaders VARCHAR(MAX)
SELECT @PivotColumnHeaders = COALESCE(@PivotColumnHeaders + ',[' + cast(Username as varchar) + ']','[' + cast(userName as varchar)+ ']')
FROM  ActivityTracking.DIM_User 
where UserKey not in (2,7,1,0)

DECLARE @PivotTableSQL NVARCHAR(MAX)

SET @PivotTableSQL = N'SELECT     [Date], DocId, UserName
FROM         (SELECT     CAST(DEA.UTCEventTimestamp AS date) AS [Date], DD.DocId, DU.UserName
                       FROM          ActivityTracking.F_DocumentEventActions AS DEA INNER JOIN
                                              ActivityTracking.DIM_Document AS DD ON DD.DocumentDWKey = DEA.DocumentDWKey INNER JOIN
                                              ActivityTracking.DIM_User AS DU ON DEA.UserKey = DU.UserKey INNER JOIN
                                              ActivityTracking.DIM_Tag AS DT ON DT.TagKey = DEA.TagKey
                       WHERE      DEA.UserKey NOT IN (2, 7, 1) AND DT.TagGroupId IS NOT NULL)as PivotData
                       
                       
                      Pivot
                      (
						Count(Docid) 
						For [UserName] In(' + @PivotColumnHeaders + ') AS PivotTable
						'


                       


EXECUTE(@PivotTableSQL)
                

Open in new window





If I remark out the:

                    
      --                Pivot
      --                (
						--Count(Docid) 
						--For [UserName] In(' + @PivotColumnHeaders + ') AS PivotTable
						-

Open in new window

The statement runs as expected.

Date       DocId       UserName
---------- ----------- ---------------------------------------------------------------
2018-01-07 42726       UserD
2018-01-10 41823       UserD
2018-01-10 41823       UserD
2018-01-10 41823       UserD
2018-01-08 41401       UserD
2018-01-08 41398       UserD
2018-01-08 41396       UserD
2018-01-08 41279       UserD
2018-01-10 41230       UserD
2018-01-10 41230       UserD
2018-01-10 41230       UserD
2018-01-10 41227       UserD
2018-01-10 41227       UserD
2018-01-10 41227       UserD
2018-01-10 41222       UserD
2017-12-28 12          UserA
2017-12-26 11          UserA
2017-12-28 11          UserA
2017-12-28 11          UserA
2017-12-28 11          UserA
2017-12-28 11          UserA
2017-12-26 10          UserA
2017-12-28 10          UserA
2017-12-28 10          UserA
2017-12-28 10          UserA
2017-12-28 10          UserA
2017-12-26 9           UserA
2017-12-26 8           UserA
2017-12-26 8           UserA
2017-12-28 8           UserA
2017-12-28 8           UserA
2017-12-26 7           UserA
2017-12-26 7           UserA
2017-12-28 7           UserA
2017-12-28 7           UserA
2017-12-26 6           UserA
2017-12-26 6           UserA
2017-12-28 6           UserA
2017-12-28 6           UserA
2017-12-21 5           UserA
2017-12-21 4           UserA
2017-12-21 3           Userb
2017-12-21 3           Userb
2017-12-21 3           UserA
2017-12-21 2           UserC
2017-12-21 1           UserC


Here is the Part that builds the dynamic headers
DECLARE @PivotColumnHeaders VARCHAR(MAX)
SELECT @PivotColumnHeaders = COALESCE(@PivotColumnHeaders + ',[' + cast(Username as varchar) + ']','[' + cast(userName as varchar)+ ']')
FROM  ActivityTracking.DIM_User 
where UserKey not in (2,7,1,0)

Open in new window


[UserA],[UserB],[UserC],[UserD]
Dataset1.txt
LVL 27
yo_beeDirector of Information TechnologyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dustin SaundersDirector of OperationsCommented:
Missing a closing parenthesis on your Pivot?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
yo_beeDirector of Information TechnologyAuthor Commented:
Added the )) as PivotTable and now I am getting:

Msg 207, Level 16, State 1, Line 1
Invalid column name 'DocId'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'UserName'.

DECLARE @PivotColumnHeaders VARCHAR(MAX)
SELECT @PivotColumnHeaders = COALESCE(@PivotColumnHeaders + ',[' + cast(Username as varchar) + ']','[' + cast(userName as varchar)+ ']')
FROM  ActivityTracking.DIM_User 
where UserKey not in (2,7,1,0)

DECLARE @PivotTableSQL NVARCHAR(MAX)

SET @PivotTableSQL = N'SELECT     [Date], DocId, UserName
FROM         (SELECT     CAST(DEA.UTCEventTimestamp AS date) AS [Date], DD.DocId, DU.UserName
                       FROM          ActivityTracking.F_DocumentEventActions AS DEA INNER JOIN
                                              ActivityTracking.DIM_Document AS DD ON DD.DocumentDWKey = DEA.DocumentDWKey INNER JOIN
                                              ActivityTracking.DIM_User AS DU ON DEA.UserKey = DU.UserKey INNER JOIN
                                              ActivityTracking.DIM_Tag AS DT ON DT.TagKey = DEA.TagKey
                       WHERE      DEA.UserKey NOT IN (2, 7, 1) AND DT.TagGroupId IS NOT NULL)as PivotData
                       
                       
                      Pivot
                      (
						Count(Docid) 
						For [UserName] In(' + @PivotColumnHeaders + ')) AS PivotTable
						'


                       


EXECUTE(@PivotTableSQL)
                

Open in new window

0
Dustin SaundersDirector of OperationsCommented:
Ok, so I imported your Dataset1 and I think what you want is this (correct me if I'm wrong):

For each Username in results, show the total number of dates that correlate to the document ID.  Is that correct?

This is based off Dataset1.txt

So, to dynamically get the UserNames:
DECLARE @PivotColumnHeaders VARCHAR(MAX), @Query AS VARCHAR(MAX)

SET @PivotColumnHeaders = STUFF((SELECT DISTINCT ',' + QUOTENAME(UserName)
							FROM Dataset1
							FOR XML PATH (''),TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')

Open in new window


And the query:
SET @Query = 'SELECT DocID,' + @PivotColumnHeaders + ' FROM
			(
				SELECT DocID, UserName, Date
				FROM DataSet1
			) x
			PIVOT
			(
				COUNT(Date)
				FOR UserName in (' + @PivotColumnHeaders + ')
			) p'

Open in new window


I'm counting the results of Date so its grouped by DocID.

So
DECLARE @PivotColumnHeaders VARCHAR(MAX), @Query AS VARCHAR(MAX)

SET @PivotColumnHeaders = STUFF((SELECT DISTINCT ',' + QUOTENAME(UserName)
							FROM Dataset1
							FOR XML PATH (''),TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')

SET @Query = 'SELECT DocID,' + @PivotColumnHeaders + ' FROM
			(
				SELECT DocID, UserName, Date
				FROM DataSet1
			) x
			PIVOT
			(
				COUNT(Date)
				FOR UserName in (' + @PivotColumnHeaders + ')
			) p'

EXECUTE(@Query)

Open in new window


Give me the results:
pivot.png
0
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

yo_beeDirector of Information TechnologyAuthor Commented:
Why do we have to build the dynamic that ver. the why I posted it.
0
Dustin SaundersDirector of OperationsCommented:
I'm not sure I understand that question?
0
yo_beeDirector of Information TechnologyAuthor Commented:
You modified my statement  with

1:DECLARE @PivotColumnHeaders VARCHAR(MAX), @Query AS VARCHAR(MAX)
2:
3:SET @PivotColumnHeaders = STUFF((SELECT DISTINCT ',' + QUOTENAME(UserName)
4:							FROM Dataset1
5:							FOR XML PATH (''),TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')

Open in new window


which is different than:
DECLARE @PivotColumnHeaders VARCHAR(MAX)
SELECT @PivotColumnHeaders = COALESCE(@PivotColumnHeaders + ',[' + cast(Username as varchar) + ']','[' + cast(userName as varchar)+ ']')
FROM  ActivityTracking.DIM_User 
where UserKey not in (2,7,1,0)

Open in new window

0
yo_beeDirector of Information TechnologyAuthor Commented:
Got it to work

DECLARE @PivotColumnHeaders VARCHAR(MAX)
SELECT @PivotColumnHeaders = COALESCE(@PivotColumnHeaders + ',[' + cast(Username as varchar) + ']','[' + cast(userName as varchar)+ ']')
FROM  ActivityTracking.DIM_User 
where UserKey not in (2,7,1,0)

DECLARE @PivotTableSQL NVARCHAR(MAX)

SET @PivotTableSQL = N'SELECT     *
FROM         (SELECT     CAST(DEA.UTCEventTimestamp AS date) AS [Date], DD.DocId, DU.UserName
                       FROM          ActivityTracking.F_DocumentEventActions AS DEA INNER JOIN
                                              ActivityTracking.DIM_Document AS DD ON DD.DocumentDWKey = DEA.DocumentDWKey INNER JOIN
                                              ActivityTracking.DIM_User AS DU ON DEA.UserKey = DU.UserKey INNER JOIN
                                              ActivityTracking.DIM_Tag AS DT ON DT.TagKey = DEA.TagKey
                       WHERE      DEA.UserKey NOT IN (2, 7, 1) AND DT.TagGroupId IS NOT NULL)as PivotData
                       
                       
                      Pivot
                      (
						Count(Docid) 
						For [UserName] In(' + @PivotColumnHeaders + ')
						) AS PivotTable
						'


                       


EXECUTE(@PivotTableSQL)
                

Open in new window

After adding the ) at the end I had to change the Select [Docid],[Date],[Username] to  Select *
0
yo_beeDirector of Information TechnologyAuthor Commented:
Thanks you for finding that syntax.  

Great help
0
Dustin SaundersDirector of OperationsCommented:
No problem, glad you got it going!

To answer your question, since I didn't have DIM_User table, I had to modify the sample to show you results.  (All I had of your data is Dataset1.txt, so I just needed the distinct users CSV separated.)  What you had originally would work fine with that table (assuming it's unique records).
0
yo_beeDirector of Information TechnologyAuthor Commented:
I will work on the VB.net question later.  
Thanks for all your help
0
yo_beeDirector of Information TechnologyAuthor Commented:
I am adding a follow up question to sum the columns.
Hope you can assist me with that as well.
0
Dustin SaundersDirector of OperationsCommented:
You can use a cte to do that, then select from the result and union a total row.

Basically, just wrap your query in
WITH cte AS ( <your query> )

Open in new window


Then SELECT that result, and UNION ALL with another SELECT for the totals.  In the query I just replace the column headers '[' with 'SUM([' and then ']' with '])' .

Example with my previous query w/ Dataset1.

SET @Query = 'WITH cte AS (
			SELECT DocID,' + @PivotColumnHeaders + ' FROM
			(
				SELECT DocID, UserName, Date
				FROM DataSet1
			) x
			PIVOT
			(
				COUNT(Date)
				FOR UserName in (' + @PivotColumnHeaders + ')
			) p
		)
		SELECT * FROM cte
		UNION ALL
		SELECT ''TOTAL'',' + REPLACE(REPLACE(@PivotColumnHeaders,'[','SUM(['),']','])') + ' FROM cte'

Open in new window


Gives me this result:
sum1.png
0
yo_beeDirector of Information TechnologyAuthor Commented:
I get this error when I run the CTE

DECLARE @PivotColumnHeaders VARCHAR(MAX)
SELECT @PivotColumnHeaders = COALESCE(@PivotColumnHeaders + ',[' + cast(Lastname + ', ' + Firstname as varchar) + ']','[' + cast(Lastname + ', ' + Firstname as varchar)+ ']')
FROM  ActivityTracking.DIM_User 
where UserKey not in (2,7,1,0)

DECLARE @PivotTableSQL NVARCHAR(MAX)

SET @PivotTableSQL = N'With CTE AS (SELECT     *
FROM         (SELECT     CAST(DEA.UTCEventTimestamp AS date) AS [Date], DD.DocId, DU.Lastname + '', '' + du.Firstname as username
                       FROM          ActivityTracking.F_DocumentEventActions AS DEA INNER JOIN
                                              ActivityTracking.DIM_Document AS DD ON DD.DocumentDWKey = DEA.DocumentDWKey INNER JOIN
                                              ActivityTracking.DIM_User AS DU ON DEA.UserKey = DU.UserKey INNER JOIN
                                              ActivityTracking.DIM_Tag AS DT ON DT.TagKey = DEA.TagKey
                       WHERE      DEA.UserKey NOT IN (2, 7, 1) AND DT.TagGroupId IS NOT NULL)as PivotData
                       
                       
                      Pivot
                      (
						Count(Docid) 
						For [UserName] In(' + @PivotColumnHeaders + ')
						) AS PivotTable)
						--Group by username with ROLLUP
						--order by [Date]--,Grouping(Username)
						Select * From CTE
						Union All
					SELECT ''TOTAL'',' + REPLACE(REPLACE(@PivotColumnHeaders,'[','SUM(['),']','])') + ' FROM cte
						'
						

                       


EXECUTE(@PivotTableSQL)
                

Open in new window


Conversion failed when converting date and/or time from character string.
0
Dustin SaundersDirector of OperationsCommented:
I see, so you're doing DocID by Date rather than Date by DocID.

If your query is casting the first column as date, then you can't use 'TOTAL' in your union.  If you wanted to do that, you would need to either not cast DEA.UTCEventTimestamp as DATETIME and use VARCHAR, cast the Date as VARCHAR in your cte select (if you're going to order by or select a range), or you could just change 'TOTAL' to NULL.  But those columns would need to be the same type.

query5.png
0
yo_beeDirector of Information TechnologyAuthor Commented:
Thanks

I will try that later tonight
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.