DECLARE @query VARCHAR(max)

declare @cols VARCHAR(max)

set @cols=dbo.[getAllDaysInYear]()

print @cols

SET @query = N'SELECT userid, '+@cols +'

FROM

( select userId,CONVERT(VARCHAR(10), loggedDate, 120) as loggedDate,logCount from loginStats

)

AS Q1

PIVOT

(

sum( logCount) FOR loggedDate IN ('+@cols +')

) AS Q2'

print @query

EXECUTE(@query)

when trying to execute the above Query I'm unable to execute the Query,

Below is the funtion used in the Query.

alter FUNCTION [dbo].[getAllDaysInYear] ( )

RETURNS varchar(8000)

AS

BEGIN

DECLARE @STR varchar(8000)

set @STR='';

with x (dy, yr) as ( select dy, year (dy) yr from

( select getdate () - datepart (dy, getdate ()) + 1 dy

-- the first date of the current year

) tmp1 union all select dateadd (dd, 1, dy), yr from x where year (dateadd (dd, 1, dy)) = yr )

SELECT @STR=@str+','+'['+ LEFT(CONVERT(VARCHAR,x.dy, 120), 10)+']' from x option (maxrecursion 400)

RETURN LTRIM(RTRIM(substring(@str,2,len(@str))))

END