I have this dynamic query:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@archivedate date = '5/1/2019';
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
SELECT TOP((SELECT TOP (1) COUNT(DISTINCT ratechangedate) datecount
FROM MARS_DW.[dbo].[vw_GTMScheduledRateAndPaymentChangesWithAccountNumber_Archive]
WHERE ArchiveDate = @archivedate
GROUP BY account
ORDER BY datecount DESC)) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
FROM E4
)
SELECT @cols = (SELECT REPLACE( '
,MIN( CASE WHEN index_num = <<index_num>> THEN ratechangedate END) AS [date <<index_num>>]
,MIN( CASE WHEN index_num = <<index_num>> THEN new_noterate END) AS [rate <<index_num>>]' , '<<index_num>>', n)
FROM cteTally
ORDER BY n
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
set @query =
N'WITH a AS (
SELECT a.account,
dense_rank() OVER ( PARTITION BY a.account ORDER BY ratechangedate) AS index_num,
CONVERT(VARCHAR(10),ratechangedate,101)[ratechangedate],
new_noterate
FROM MARS_DW.[dbo].[vw_GTMScheduledRateAndPaymentChangesWithAccountNumber_Archive] a
WHERE archivedate = @date
)
SELECT a.account' + @cols + N'
FROM a
GROUP BY a.Account;'
EXECUTE sp_executesql @query, N'@date datetime', @date = @archivedate;
What I have tried:
I want to remove the `NULL` values and replace it with an empty string I tried just putting `ISNULL(CONVERT(VARCHAR(10),ratechangedate,101),'')` and `ISNULL(new_noterate,'')` in the @query part of the code but I get these errors:
<pre>
<pre>> Msg 207, Level 16, State 1, Line 10
> Invalid column name 'ratechangedate'.
>
> Msg 207, Level 16, State 1, Line 12
> Invalid column name 'ratechangedate'.
>
> Msg 207, Level 16, State 1, Line 14
> Invalid column name 'ratechangedate'.
> ...... and so on for every other "line" up to a value of 34 .......
Any help would be appreciated.