SQLTeam.com | Weblogs | Forums

Trouble with the PIVOT

I am attempting to pivot some data but need to have the columns across populate based on date variables so that each day in the sequence is a column. While the regular PIVOT logic seems to work fine, when I add the dynamic feature to spread the dates depending on the date window given I receive "is not a valid identifier." as part of my error message in the dynamic SQL. I think it may just be a matter of syntax but I have played with it everyway I can think of and can't seem to get this dynamic pivot query to run. Can you help?
If so, it would be greatly appreciated!
Thank you! Here is my code and error message...

declare @FirstDOM datetime, @LastDOM datetime

--set @FirstDOM = (select dateadd(d,-1,dateadd(mm,datediff(m,0,getdate()),1 )))
--set @LastDOM = (select dateadd(s,-1,dateadd(mm,datediff(m,0,getdate())+1,0)))

--for testing the pivot
SET @FirstDOM = '2/1/2022' SET @LastDOM = '2/28/2022'

DECLARE @StartDate datetime SET @StartDate = (SELECT DATEADD(mm,-1,@FirstDOM))
DECLARE @EndDate datetime SET @EndDate = (SELECT DATEADD(mm,-1,@LastDOM))
DECLARE @DatesToUse varchar(500)
DECLARE @Dates table ([Date] datetime)
INSERT @Dates (Date) SELECT DISTINCT [Date] FROM [dbo].[PJMInvoiceActuals]
--SELECT * FROM @Dates WHERE [Date] > @StartDate AND [Date] < @EndDate
DECLARE @FormatedDates table ([Date] varchar(30))
INSERT @FormatedDates

SELECT DISTINCT CONVERT (DATE,[Date])AS Date FROM @Dates WHERE [Date] BETWEEN @StartDate AND @EndDate
SELECT * FROM @FormatedDates
DECLARE @colname varchar(500)
SET @colname = NULL
SELECT @colname = COALESCE(@colname + ',','') + QUOTENAME([Date])
FROM @FormatedDates;
SELECT @colname
--SELECT @colname
DECLARE @SQLQuery NVARCHAR(MAX)

SET @SQLQuery =
'with pivot_tbl as
(
SELECT [BillingLineItemType], '+@colname+'
FROM
(
SELECT CONVERT(DATE,[Date]) AS Date,[BillingLineItemType], Amount
FROM [dbo].[PJMInvoiceActuals]
WHERE CONVERT(DATE,[Date] ) BETWEEN ''2022-01-01'' AND ''2022-01-31''
) AS SourceTable
PIVOT
( Sum(Amount)
FOR [Date] IN ('+@colname+')
) AS PivotTable
)
--SELECT * FROM pivot_tbl'
EXEC @SQLQuery

--error msg

Msg 203, Level 16, State 2, Line 46
The name 'with pivot_tbl as
(
SELECT [BillingLineItemType], [2022-01-01],[2022-01-02],[2022-01-03],[2022-01-04],[2022-01-05],[2022-01-06],[2022-01-07],[2022-01-08],[2022-01-09],[2022-01-10],[2022-01-11],[2022-01-12],[2022-01-13],[2022-01-14],[2022-01-15],[2022-01-16],[2022-01-17],[2022-01-18],[2022-01-19],[2022-01-20],[2022-01-21],[2022-01-22],[2022-01-23],[2022-01-24],[2022-01-25],[2022-01-26],[2022-01-27],[2022-01-28]
FROM
(
SELECT CONVERT(DATE,[Date]) AS Date,[BillingLineItemType], Amount
FROM [dbo].[PJMInvoiceActuals]
WHERE CONVERT(DATE,[Date] ) BETWEEN '2022-01-01' AND '2022-01-31'
) AS Sourc' is not a valid identifier.

hi

please see if the @SQLQuery is getting truncated

example
set @message = 'aaa bb cc 123456 yyy'
but
@message will be ''aaa bb cc 123456' it gets truncated becuase of length issues

that could be one reason

Please post content of @SQLQuery
By doing Print @SQLQuery

looks like you do not need this section

;with pivot_tbl as
(