[Solved] Making a dynamic pivot table but without using the fiddle

by


It would be a lot easier for you to create a cross tabs query. If you can identify the pattern, the dynamic code can be easier to code and there are multiple examples on the internet (and this site). If you don’t know how to create dynamic code, I’d suggest that you stay away from it until you fully understand the do’s and don’ts.

WITH a
AS (
    SELECT a.account,
        dense_rank() OVER ( PARTITION BY a.account ORDER BY ratechangedate) AS index_num,
        ratechangedate,
        new_noterate
    FROM MARS_DW.[dbo].[vw_GTMScheduledRateAndPaymentChangesWithAccountNumber_Archive] a
    INNER JOIN (
        SELECT *
        FROM mars..vw_loans
        WHERE loanstatus <> 'bk payment plan'
        ) b ON a.account = b.account
    WHERE archivedate="5/20/2019"
    )
SELECT a.Account,
    MIN( CASE WHEN index_num = 1 THEN ratechangedate END) AS [date 1],
    MIN( CASE WHEN index_num = 1 THEN new_noterate END)   AS [rate 1],
    MIN( CASE WHEN index_num = 2 THEN ratechangedate END) AS [date 2],
    MIN( CASE WHEN index_num = 2 THEN new_noterate END)   AS [rate 2],
    MIN( CASE WHEN index_num = 3 THEN ratechangedate END) AS [date 3],
    MIN( CASE WHEN index_num = 3 THEN new_noterate END)   AS [rate 3],
    MIN( CASE WHEN index_num = 4 THEN ratechangedate END) AS [date 4],
    MIN( CASE WHEN index_num = 4 THEN new_noterate END)   AS [rate 4],
    MIN( CASE WHEN index_num = 5 THEN ratechangedate END) AS [date 5],
    MIN( CASE WHEN index_num = 5 THEN new_noterate END)   AS [rate 5],
    MIN( CASE WHEN index_num = 6 THEN ratechangedate END) AS [date 6],
    MIN( CASE WHEN index_num = 6 THEN new_noterate END)   AS [rate 6],
    MIN( CASE WHEN index_num = 7 THEN ratechangedate END) AS [date 7],
    MIN( CASE WHEN index_num = 7 THEN new_noterate END)   AS [rate 7],
    MIN( CASE WHEN index_num = 8 THEN ratechangedate END) AS [date 8],
    MIN( CASE WHEN index_num = 8 THEN new_noterate END)   AS [rate 8]
FROM a
GROUP BY a.Account;

UPDATE:

I told you to read the article on how to work with dynamic SQL. I also told you to identify the pattern, you didn’t. Now, there’s an answer that will be much slower than what I’m suggesting and I just don’t want subpar code to propagate, so here’s an option.

DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX),
        @archivedate AS DATETIME = '20190520'; --Always use ISO 8601 format YYYYMMDD

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 AND AppliedDate > '1/2/2018'
                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,
        ratechangedate,
        new_noterate
    FROM MARS_DW.[dbo].[vw_GTMScheduledRateAndPaymentChangesWithAccountNumber_Archive] a
    INNER JOIN (
        SELECT *
        FROM mars..vw_loans
        WHERE loanstatus <> ''bk payment plan''
        ) b ON a.account = b.account
    WHERE archivedate = @date
    )
SELECT a.Account' + @cols + N'
FROM a
GROUP BY a.Account;'


EXECUTE sp_executesql @query, N'@date datetime', @date = @archivedate;

13

solved Making a dynamic pivot table but without using the fiddle