SQL Pivot help

try this link for dynamic pivot tables
http://www.sqlhub.com/2010/06/pivot-task-in-data-flow-transformation.html
http://www.sqlhub.com/2009/05/generic-stored-procedure-for-pivot-in.html
http://www.sqlhub.com/2010/08/for-xml-path-root-in-sql-server-2008.html

Try this.

DECLARE @FiscalWk1 VARCHAR(MAX)
SELECT  @FiscalWk1 = STUFF(( SELECT DISTINCT '],[' + b.FiscalWk1
                        from  (select [SR Acceptor Name], 
							[SR Number], 
							Convert(Varchar(10),([SR Accept Date Time]), 101) as [SR Accept Date Time]
							from Reports.dbo.Productivity 
							where Reports.dbo.Productivity.[SR Acceptor Name] in 
							(select BacklogName from PulseUsertable where
							Team ='BNG_NAS_A' ) ) as a 
							Left join (select FiscalDate, FiscalWk1, FiscalWKYR from Reports.dbo.FiscalCalender) as b
							on a.[SR Accept Date Time] = b.[FiscalDate] Group by B.FiscalWk1, [SR Acceptor Name]
                        ORDER BY '],[' + b.FiscalWk1
                        FOR XML PATH('')), 1, 2, '') + ']'


DECLARE @CustomQuery varchar(max)
SET @CustomQuery = 'SELECT [SR Acceptor Name], '+@FiscalWk1+' 
FROM (SELECT [SR Acceptor Name], b.FiscalWk1 from  (select [SR Acceptor Name], 
							[SR Number], 
							Convert(Varchar(10),([SR Accept Date Time]), 101) as [SR Accept Date Time]
							from Reports.dbo.Productivity 
							where Reports.dbo.Productivity.[SR Acceptor Name] in 
							(select BacklogName from PulseUsertable where
							Team =''BNG_NAS_A'' ) ) as a 
							Left join (select FiscalDate, FiscalWk1, FiscalWKYR from Reports.dbo.FiscalCalender) as b
							on a.[SR Accept Date Time] = b.[FiscalDate] Group by B.FiscalWk1, [SR Acceptor Name]) AS SourceTable 
PIVOT 
(COUNT(a.[SR Number]) 
FOR b.FiscalWk1 IN ('+@FiscalWk1+')) AS PivotTable'

EXEC (@CustomQuery)

Select allOpen in new window

Dharam - Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'FOR'.

OK, because the PIVOT requires columns to be "known" then you will need to resolve the last 6 weeks before hand and use dynamic sql to resolve the PIVOT query itself.

The steps are shown in : https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/A_653-Dynamic-Pivot-Procedure-for-SQL-Server.html

Not sure what datatypes are involved, but if converting dates then better to use style code 112 for dynamic conversion to other date fields, but that is a different story maybe :)

-- now we dont really need the subqueries, and if checking against fiscalcalender then should be inner join

select  [SR Acceptor Name],b.FiscalWk1
from reports.dbo.Productivity a
inner join Reports.dbo.FiscalCalender b on convert(varchar(10),a.[SR Accept Date Time],101) = b.[FiscalDate] 
where a.[SR Acceptor Name] in (select BacklogName from PulseUsertable where team ='BNG_NAS_A' )
and a.[SR Accept Date Time] >= dateadd(week,-7,getdate())

-- the above should give our "raw" data that we need to pivot - dont worry about group by - the PIVOT does aggregation for us

-- now our query - first the columns

declare @columns varchar(1000)
select @columns = isnull(@columns+',','') + '['+fiscalwk1+']' from Reports.dbo.FiscalCalender where fiscaldate >= dateadd(week,-7,getdate()) order by fiscaldate asc 

exec('select * from
(select  [SR Acceptor Name],b.FiscalWk1
 from reports.dbo.Productivity a 
 inner join Reports.dbo.FiscalCalender b on convert(varchar(10),a.[SR Accept Date Time],101) = b.[FiscalDate] 
 where a.[SR Acceptor Name] in (select BacklogName from PulseUsertable where team =''BNG_NAS_A'' )
 and a.[SR Accept Date Time] >= dateadd(week,-7,getdate()) ) srce
pivot
(count(fiscalwk1) for fiscalwk1 in ('+@columns+')) pvt')

Select allOpen in new window

Oh, and might need to finetune : fiscaldate >= dateadd(week,-7,getdate())

Not sure what week commencings you have.

Might even consider adding days, or, selecting top 6 etc... For example :

declare @columns varchar(1000)
select @columns = isnull(@columns+',','') + '['+fiscalwk1+']' from (select top 6 fiscalwk1 from reports.dbo.fiscalcalendar where fiscaldate >= dateadd(week,-7,getdate())) a order by fiscalwk1 asc
print @columns


It is the columns that becomes sensitive to what will actually show, so have to get them right. In the body of the dynamic SQL we do limit the selection by limiting the rows returned for .[SR Accept Date Time]  but the columns need to match the last 6.

Mark -

Datatypes in the table.

FiscalDate = smalldatetime
FiscalWK1 = nvarchar(255)
BacklogName = varchar(255)

SR Acceptor Name = nvarchar(255)
SR Accept Date Time = datetime

I have weeks starting WK 22 till this week. (Week numbers are calculated based on normal calender from Sun - Sat.

Query you gave:

declare @columns varchar(1000)
select @columns = isnull(@columns+',','') + '['+fiscalwk1+']' from (select top 6 fiscalwk1 from reports.dbo.fiscalcalender where fiscaldate >= dateadd(week,-7,getdate())) a order by fiscalwk1 asc

exec('select * from (select  [SR Acceptor Name],b.FiscalWk1 from reports.dbo.Productivity a inner join
Reports.dbo.FiscalCalender b on convert(varchar(10),a.[SR Accept Date Time],101) = b.[FiscalDate]
 where a.[SR Acceptor Name] in (select BacklogName from PulseUsertable where team =''BNG_NAS_A'')
 and a.[SR Accept Date Time] >= dateadd(week,-7,getdate()) ) as srce
pivot
(count(fiscalwk1) for fiscalwk1 in ('+@columns+')) pvt')


is throwing an error:

Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'pivot'.

Whats wrong here?

OK, step by step... lets first check the columns.

declare @columns varchar(1000)
select @columns = isnull(@columns+',','') + '['+fiscalwk1+']' from (select top 6 fiscalwk1 from reports.dbo.fiscalcalender where fiscaldate >= dateadd(week,-7,getdate())) a order by fiscalwk1 asc
print @columns

Result : [WK45]

However i tried your previous @columns syntax


declare @columns varchar(1000)
select @columns = isnull(@columns+',','') + '['+fiscalwk1+']' from Reports.dbo.FiscalCalender where fiscaldate >= dateadd(week,-7,getdate()) order by fiscaldate asc
print @columns


Result:
[WK44],[WK44],[WK44],[WK45],[WK45],[WK45],[WK45],[WK45],[WK45],[WK45],[WK46],[WK46],[WK46],[WK46],[WK46],[WK46],[WK46],[WK47],[WK47],[WK47],[WK47],[WK47],[WK47],[WK47],[WK48],[WK48],[WK48],[WK48],[WK48],[WK48],[WK48],[WK49],[WK49],[WK49],[WK49],[WK49],[WK49],[WK49],[WK50],[WK50],[WK50],[WK50],[WK50],[WK50],[WK50],[WK51],[WK51],[WK51],[WK51],[WK51],[WK51],[WK51],[WK52],[WK52],[WK52],[WK52],[WK52],[WK52],[WK52],[WK53],[WK53],[WK53],[WK53],[WK53],[WK53],[WK53],[WK01],[WK01],[WK01],[WK01],[WK01],[WK01],[WK01],[WK02],[WK02],[WK02],[WK02],[WK02],[WK02],[WK02],[WK03],[WK03],[WK03],[WK03],[WK03],[WK03],[WK03],[WK04],[WK04],[WK04],[WK04],[WK04],[WK04],[WK04],[WK05],[WK05],[WK05],[WK05],[WK05],[WK05],[WK05],[WK06],[WK06],[WK06],[WK06],[WK06],[WK06],[WK06],[WK07],[WK07],[WK07],[WK07],[WK07],[WK07],[WK07],[WK08],[WK08],[WK08],[WK08],[WK08],[WK08],[WK08],[WK09],[WK09],[WK09],[WK09],[WK09],[WK09],[WK09],[WK10],[WK10],[WK10],[WK10],[WK10],[WK10],[WK10],[WK11],[WK11],[WK11],[WK11],[WK11],[WK11],[WK11]

OK,

Understand the problem, so, lets try :

declare @columns varchar(1000)
select @columns = isnull(@columns+',','') + '['+fiscalwk1+']' from (select top 6 fiscalwk1 from reports.dbo.fiscalcalender where fiscaldate between dateadd(week,-7,getdate()) and getdate() group by fiscalwk1) a order by fiscalwk1 asc
print @columns

Result:

[WK44],[WK45],[WK46],[WK47],[WK48],[WK49]

I tried running the full script.

declare @columns varchar(1000)
select @columns = isnull(@columns+',','') + '['+fiscalwk1+']' from (select top 6 fiscalwk1 from reports.dbo.fiscalcalender where fiscaldate between dateadd(week,-7,getdate()) and getdate() group by fiscalwk1) a order by fiscalwk1 asc


exec('select * from (select  [SR Acceptor Name],b.FiscalWk1 from reports.dbo.Productivity a inner join
Reports.dbo.FiscalCalender b on convert(varchar(10),a.[SR Accept Date Time],101) = b.[FiscalDate]
 where a.[SR Acceptor Name] in (select BacklogName from PulseUsertable where team =''BNG_NAS_A'')
 and a.[SR Accept Date Time] >= dateadd(week,-7,getdate()) ) as srce
pivot
(count(fiscalwk1) for fiscalwk1 in ('+@columns+')) pvt')



still throws an error at Pivot:

Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'pivot'.

OK, we are just going step at a time :)

And guess we should clarify your version about now as well... Also the compatability level of your database (in SSMS right click and go into properties)

Next step is trying it 'longhand' :

select * from
(
 select  a.[SR Acceptor Name], b.FiscalWk1
 from reports.dbo.Productivity a
 inner join reports.dbo.FiscalCalender b on convert(varchar,a.[SR Accept Date Time],112) = b.[FiscalDate]
 where a.[SR Acceptor Name] in (select BacklogName from PulseUsertable where team ='BNG_NAS_A')
 and a.[SR Accept Date Time] >= dateadd(week,-7,getdate())
) as srce
pivot
(
 count(fiscalwk1) for fiscalwk1 in ([WK44],[WK45],[WK46],[WK47],[WK48],[WK49])
) pvt

I have SQL 2008 R2.

I ran just this query:

 select  a.[SR Acceptor Name], b.FiscalWk1
 from reports.dbo.Productivity a
 inner join reports.dbo.FiscalCalender b on convert(varchar,a.[SR Accept Date Time],112) = b.[FiscalDate]
 where a.[SR Acceptor Name] in (select BacklogName from PulseUsertable where team ='BNG_NAS_A')
 and a.[SR Accept Date Time] >= dateadd(week,-7,getdate())

Result is good.

But when i ran with Pivot query, its still giving the same error.

Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near 'pivot'.

Did you check the compatibility level of your database ?  It has to be >= 90 for the PIVOT to work.

See : http://msdn.microsoft.com/en-us/library/bb510680.aspx

If the current compatibility is not 90 or higher, are you in a position to change it ? (the above link lists the differences and possible impact). Normally it works fine.

If cannot change, then we will have to change direction. I will start working on an alternate just in case...

I seem to vaguely recall one issue about prefixed columns - getting confused with one of the service pack updates. So if it is compatibility level 90 or higher, can also try :

select * from 
(
 select  [SR Acceptor Name] as SR_Name, FiscalWk1 as FiscalWk, 1 as counter
 from reports.dbo.Productivity a 
 inner join reports.dbo.FiscalCalender b on convert(varchar,a.[SR Accept Date Time],112) = b.[FiscalDate] 
 where a.[SR Acceptor Name] in (select BacklogName from PulseUsertable where team ='BNG_NAS_A')
 and a.[SR Accept Date Time] >= dateadd(week,-7,getdate()) 
) as srce
pivot
(
 count(counter) for FiscalWk in ([WK44],[WK45],[WK46],[WK47],[WK48],[WK49]) 
) as pvt

Select allOpen in new window

Bingo. you are right. mine is only 80 :( and i dont have the option in drop down.

Hmmm... thats interesting that you dont have the option.  In SSMS you should be able to right click on the database, go into properties, go to options page and it should be there, unless the server instance you are connecting to is the old 2000 instance.

Is it a local database to your SQL Server 2008 server ?

In SSMS (SQL Server Management Studio) are you connecting to a SQL Server 2008 instance, or, pointing to the old SQL 2000 instance ?

Is it a remote database - like somewhere else ?

I can write a routine - had intended to do so for some time, to go hand in hand with :  : https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/A_653-Dynamic-Pivot-Procedure-for-SQL-Server.html  but without using the PIVOT function.

Have to pop out for a little while, so, can do it on my return... Is that OK ?

Perfect. No issues at all. Thanks for helping Mark.

its a local db pointing old 2000 instance.

I'll have this question open until you return. Thanks again for your help.

Bingo. Thanks Mark. works amazingly great