Trouble with the PIVOT

• far2reel

  Valued Member

  Points: 62

  More actions

  May 8, 2022 at 5:39 pm

  #4029108

  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 recieve "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!

  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

  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.

• Eddie Wuerch

  SSChampion

  Points: 12541

  More actions

  May 8, 2022 at 6:17 pm

  #4029110

  If the connection's setting for CONCAT_NULL_YIELDS_NULL is ON (which must often be set to ON depending on use of other features), then adding a NULL string to a value string will produce a NULL string.

  You must initialize @colName to something besides NULL for the COALESCE to produce anything but NULL.

  DECLARE @colname varchar(500);
  -- The next line should be changed; setting @colName to NULL 
  -- will always produce NULL when values are concatenated to it.
  SET @colname = NULL 
  -- Instead, initialize @colName with an empty string to make the COALESCE work:
  SELECT @colName = '';
  
  SELECT @colname = COALESCE(@colname + ',','') + QUOTENAME([Date])
    FROM @FormatedDates;

  Eddie Wuerch
  MCM: SQL