Making a dynamic pivot table but without using the fiddle

Question

I have done this:

;WITH a AS
(
    SELECT
        a.account
        ,index_num_date = 'date ' + CONVERT(VARCHAR(30), DENSE_RANK() OVER ( PARTITION BY a.account ORDER BY ratechangedate ))
        ,index_num_rate = 'rate ' + CONVERT(VARCHAR(30), DENSE_RANK() OVER ( PARTITION BY a.account ORDER BY ratechangedate ))
        ,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'
)
,q1 AS
(
    SELECT DISTINCT
        account
        ,ratechangedate
        ,index_num_date
    FROM a
)
,q2 AS
(
    SELECT DISTINCT
        account
        ,new_noterate
        ,index_num_rate
    FROM a
)
,datepivot AS
(
    SELECT DISTINCT
        account
        ,[date 1]
        ,[date 2]
        ,[date 3]
        ,[date 4]
        ,[date 5]
        ,[date 6]
        ,[date 7]
        ,[date 8]
        ,[date 9]
        ,[date 10]
        ,[date 11]
        ,[date 12]
        ,[date 13]
    FROM q1
        PIVOT
        (
            MIN(ratechangedate)
            FOR index_num_date IN ( [date 1]
                ,[date 2]
                ,[date 3]
                ,[date 4]
                ,[date 5]
                ,[date 6]
                ,[date 7]
                ,[date 8]
                ,[date 9]
                ,[date 10]
                ,[date 11]
                ,[date 12]
                ,[date 13]
            )
        ) pvt1
)
,ratepivot AS
(
    SELECT DISTINCT
        account
        ,[rate 1]
        ,[rate 2]
        ,[rate 3]
        ,[rate 4]
        ,[rate 5]
        ,[rate 6]
        ,[rate 7]
        ,[rate 8]
        ,[rate 9]
        ,[rate 10]
        ,[rate 11]
        ,[rate 12]
        ,[rate 13]
    FROM q2
        PIVOT
        (
            MIN(new_noterate)
            FOR index_num_rate IN ( [rate 1]
                ,[rate 2]
                ,[rate 3]
                ,[rate 4]
                ,[rate 5]
                ,[rate 6]
                ,[rate 7]
                ,[rate 8]
                ,[rate 9]
                ,[rate 10]
                ,[rate 11]
                ,[rate 12]
                ,[rate 13]
            )
        ) pvt2
)
SELECT
    a.Account
    ,[date 1]
    ,[rate 1]
FROM datepivot a
LEFT JOIN ratepivot b ON a.Account = b.Account

Which gives me this

But this is not dynamic and my Microsoft version 2016 does not allow me to use the Fiddle thing recommended in various posts. Thus I was recommended to use the Coalesce() function but I have no idea what to do to make the latter dynamic. Any help (not referring to some post) would really help.

Update:

Following the comment I tried this:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.Account) 
            FROM MARS_DW.[dbo].[vw_GTMScheduledRateAndPaymentChangesWithAccountNumber_Archive] c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT Account, ' + @cols + ' from 
            (
                select Account
                    , ratechangedate
                    , new_noterate
                from  MARS_DW.[dbo].[vw_GTMScheduledRateAndPaymentChangesWithAccountNumber_Archive]
           ) x
            pivot 
            (
                 min(ratechangedate)
                for category in (' + @cols + ')
            ) p '

but get this error:

Msg 1056, Level 15, State 1, Line 37
The number of elements in the select list exceeds the maximum allowed number of 4096 elements.
Msg 102, Level 15, State 1, Line 43
Incorrect syntax near 'x'.

Update:

I tried this to limit the amount

DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.RateChangeDate) 
            FROM MARS_DW.[dbo].[vw_GTMScheduledRateAndPaymentChangesWithAccountNumber_Archive] c
            WHERE c.ArchiveDate = '5/21/2019' AND c.AppliedDate > '1/2/2018'
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT Account, ' + @cols + ' from 
            (
                select Account
                    , ratechangedate
                    , new_noterate
                from  MARS_DW.[dbo].[vw_GTMScheduledRateAndPaymentChangesWithAccountNumber_Archive]
           ) x
            pivot 
            (
                 min(ratechangedate)
                for category in (' + @cols + ')
            ) p 
            pivot
            (
                min(new_noterate)
                for category in (' + @cols + ')
            )

            '


execute(@query)

But I am getting this error:

Msg 102, Level 15, State 1, Line 52
Incorrect syntax near ')'.

As requested here is the top 10 from the data table

To make your pivot dynamic, you'd use dynamic sql. Fiddle usually is a reference to https://dbfiddle.uk/. [@Taryn--DBA at SO--](https://stackoverflow.com/users/426671/taryn) has a great answer in [this post](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query). What is it that you do not understand about it? — S3S, May 29 '19 at 19:26
You used `Account` instead of `ratechangedate` to populate `@cols`. — Luis Cazares, May 29 '19 at 21:14
Instead of using `execute(@query)` as the final statement, use `print(@query)` and find your syntax error. — digital.aaron, May 29 '19 at 21:58
The syntax error is being caused by a missing alias for your second `PIVOT` clause. However, if you add an alias to it, you'll get a new error stating the column names in second `PIVOT` are already specified in the first `PIVOT` (because you're using the same `@cols` variable to build the pivoted column names). It would really, really help if you could provide the table definition of vw_GTMScheduledRateAndPaymentChangesWithAccountNumber_Archive (i.e. column names, data types, a few example raw data rows). — digital.aaron, May 29 '19 at 22:26
@digital.aaron I am totally new to SQL what do you mean by table definition? Most of the data types are either float, dates, and varchars — , May 29 '19 at 22:45
It would be the `CREATE TABLE` statement used to create the table. If you don't know how to get this, that's not a dealbreaker. Run this query and report the results back: `SELECT TOP 10 * FROM MARS_DW.dbo.vw_GTMScheduledRateAndPaymentChangesWithAccountNumber_Archive` — digital.aaron, May 29 '19 at 23:02
Also, what, exactly, are you trying to do with this query? Is it something like "I want to get the first ratechangedate and the new_noterate for each account."? I just want to make sure you're not creating an XY Problem for yourself (see http://xyproblem.info/ for explanation). — digital.aaron, May 29 '19 at 23:06
@digital.aaron Please see edit, had to black out some stuff that is confidential. — , May 29 '19 at 23:44

Luis Cazares · Accepted Answer · 2019-05-30T19:56:50.557

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;

I dont have a choice to stay away, if I did I would stay away from SQL completely... — , May 29 '19 at 19:33
Then you have the obligation to inform yourself on how to handle dynamic SQL. Here's a good start http://www.sommarskog.se/dynamic_sql.html — Luis Cazares, May 29 '19 at 19:41
Can you just show me how to do this so I can get back with my life — , May 29 '19 at 19:42
I could, but I don't want to deprive you from learning. If you don't do it right, you could end facing a [Bobby Tables](https://bobby-tables.com/) issue. — Luis Cazares, May 29 '19 at 19:48
From what I did above in the update edit, what am I doing wrong? — , May 29 '19 at 19:49
The error says `The number of elements in the select list exceeds the maximum allowed number of 4096 elements` that means that the pivot is too wide and SQL Server won't be able to handle it. Do you have more than 2000 changes per account? You might want to limit that. — Luis Cazares, May 29 '19 at 21:12
Please see edits, if you know how to do this I think I have tried enough to compensate for the deprivation in learning, really could use an answer. — , May 29 '19 at 21:52
My "subpar" code actually works and produces the result set OP is looking for. Your solution produces a pair of columns ([date xx] and [rate xx]) for every distinct value of ratechangedate. You end up with a dimensionally huge, very sparsely populated table. My solution finds puts all the "first" rate changes in [date 1] and [rate 1], regardless of when those dates actually are. — digital.aaron, May 30 '19 at 16:25
You're taking this very personal. You should really learn instead of downvoting people that tell your code is not optimal. Not only does your code returns columns in the wrong order, it's also at least twice as slow as a cross tab alternative and has twice the reads. Your code also has the bad habit of embedding the parameters in the string instead of using parametrized queries with sp_executesql. So yeah, your code is subpar. — Luis Cazares, May 30 '19 at 18:15
@digital.aaron I have to agree with you as your code actually produces the correct result. No offense Luis but your code does not even produce close the result intended. — , May 30 '19 at 19:05
Hi Luis, I was wondering if you knew how to make sql ignore this error:Warning: Null value is eliminated by an aggregate or other SET operation. When I put this query into python it complains. I tried doing SET ANSI_WARNINGS OFF but then I get this error: Msg 1934, Level 16, State 1, Line 34 SELECT failed because the following SET options have incorrect settings: 'ANSI_WARNINGS'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. — , Jul 22 '19 at 22:37
First, you could add an ELSE to the CASE. If you can still get NULLs, add an ISNULL() with a value that can be ignored easily depending on your aggregation. For MIN, you use the top limit of the datatype, for MAX the bottom limit, for SUM you add 0, etc. — Luis Cazares, Jul 23 '19 at 13:27

digital.aaron · Answer 2 · 2019-05-30T16:50:42.370

After playing around with a temp version of your table, I think I finally figured this one out. There are a few things to note first, though.

Because you're trying to pivot two columns, you have to pivot them separately and then join the subresults back together.
Since we're going to use @cols for both pivots, we have to create a version of the @cols variable that aliases the dynamically created pivot column names for the final select.

So let's get into the code.

First, we'll create the strings that will contain our dynamically created column names.

DECLARE @colsAll AS NVARCHAR(MAX);
DECLARE @cols AS NVARCHAR(MAX);

SET @cols = 
STUFF((
        SELECT DISTINCT
            ',' + QUOTENAME(CONVERT(VARCHAR(30), DENSE_RANK() OVER ( PARTITION BY a.account ORDER BY a.ratechangedate)))
        FROM MARS_DW.dbo.vw_GTMScheduledRateAndPaymentChangesWithAccountNumber_Archive a
        INNER JOIN
        (
            SELECT account
            FROM mars..vw_loans
            WHERE loanstatus <> 'bk payment plan'
        ) b ON a.account = b.account
        WHERE
            a.ArchiveDate = '5/21/2019'
            AND a.AppliedDate > '1/2/2018'
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, ''
)

SET @colsAll = 
STUFF((
        SELECT DISTINCT
            ',' + 'd.' + QUOTENAME(CONVERT(VARCHAR(30), DENSE_RANK() OVER ( PARTITION BY account ORDER BY ratechangedate ))) + ' AS [Date'
            + CONVERT(VARCHAR(30), DENSE_RANK() OVER ( PARTITION BY account ORDER BY ratechangedate )) + '], ' + 'r.'
            + QUOTENAME(CONVERT(VARCHAR(30), DENSE_RANK() OVER ( PARTITION BY account ORDER BY ratechangedate ))) + ' AS [Rate'
            + CONVERT(VARCHAR(30), DENSE_RANK() OVER ( PARTITION BY account ORDER BY ratechangedate )) + ']'
        FROM MARS_DW.dbo.vw_GTMScheduledRateAndPaymentChangesWithAccountNumber_Archive a
        INNER JOIN
        (
            SELECT account
            FROM mars..vw_loans
            WHERE loanstatus <> 'bk payment plan'
        ) b ON a.account = b.account
        WHERE
            a.ArchiveDate = '5/21/2019'
            AND a.AppliedDate > '1/2/2018'
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, ''
)

When setting @colsAll, I'm using the table aliases that will be defined in the final select.

And now to generate the pivot:

DECLARE @query AS NVARCHAR(MAX);

SET @query = ';WITH dates as (SELECT Account, ' + @cols
             + ' from 
            (
                select a.Account
                    , a.ratechangedate
                    , DateIndex = CONVERT(VARCHAR(30), DENSE_RANK() OVER ( PARTITION BY a.account ORDER BY a.ratechangedate ))
                from  MARS_DW.dbo.vw_GTMScheduledRateAndPaymentChangesWithAccountNumber_Archive a
                INNER JOIN (SELECT account
                                FROM mars..vw_loans
                                WHERE loanstatus <> ''bk payment plan''
                            ) b ON a.account = b.account
                WHERE a.ArchiveDate = ''5/21/2019'' AND a.AppliedDate > ''1/2/2018''
           ) x

            pivot
            (
                min(ratechangedate)
                for DateIndex in (' + @cols + ')
            ) d)


            ,rates as (SELECT Account, ' + @cols
             + ' from 
            (
                select a.Account
                    , a.new_noterate
                    , RateIndex = CONVERT(VARCHAR(30), DENSE_RANK() OVER ( PARTITION BY a.account ORDER BY a.ratechangedate ))
                from  #vw_GTMScheduledRateAndPaymentChangesWithAccountNumber_Archive a
                INNER JOIN (SELECT account
                                FROM mars..vw_loans
                                WHERE loanstatus <> ''bk payment plan''
                            ) b ON a.account = b.account
                WHERE a.ArchiveDate = ''5/21/2019'' AND a.AppliedDate > ''1/2/2018''
           ) x

            pivot
            (
                min(new_noterate)
                for RateIndex in (' + @cols + ')
            ) r)


            SELECT d.Account, ' + @colsAll + '
            FROM dates d
            JOIN rates r ON d.Account = r.Account'

EXECUTE ( @query )

If you were to PRINT the query, you'd see the query that will be run. I always like to PRINT the query, and then copy and paste the result into a new query window and run it. If any errors pop up, it's easier to debug the final query than to try to debug the dynamic sql. For reference, the query that gets spit out by the above code looks like this:

;WITH dates AS
(
    SELECT
        Account
        ,[1]
        ,[2]
        ,[3]
    FROM
    (
        SELECT
            a.Account
            ,a.ratechangedate
            ,DateIndex = CONVERT(VARCHAR(30), DENSE_RANK() OVER ( PARTITION BY a.account
                                                                  ORDER BY a.ratechangedate
                                                           )
                         )
        FROM MARS_DW.dbo.vw_GTMScheduledRateAndPaymentChangesWithAccountNumber_Archive a
        INNER JOIN
        (
            SELECT account
            FROM mars..vw_loans
            WHERE loanstatus <> 'bk payment plan'
        ) b ON a.account = b.account
        WHERE
            a.ArchiveDate = '5/21/2019'
            AND a.AppliedDate > '1/2/2018'
    ) x
    PIVOT
    (
        MIN(ratechangedate)
        FOR DateIndex IN ( [1]
            ,[2]
            ,[3]
        )
    ) d
)
,rates AS
(
    SELECT
        Account
        ,[1]
        ,[2]
        ,[3]
    FROM
    (
        SELECT
            a.Account
            ,a.new_noterate
            ,RateIndex = CONVERT(VARCHAR(30), DENSE_RANK() OVER ( PARTITION BY a.account
                                                                  ORDER BY a.ratechangedate
                                                           )
                         )
        FROM #vw_GTMScheduledRateAndPaymentChangesWithAccountNumber_Archive a
        INNER JOIN
        (
            SELECT account
            FROM mars..vw_loans
            WHERE loanstatus <> 'bk payment plan'
        ) b ON a.account = b.account
        WHERE
            a.ArchiveDate = '5/21/2019'
            AND a.AppliedDate > '1/2/2018'
    ) x
    PIVOT
    (
        MIN(new_noterate)
        FOR RateIndex IN ( [1]
            ,[2]
            ,[3]
        )
    ) r
)
SELECT
    d.Account
    ,d.[1] AS Date1
    ,r.[1] AS Rate1
    ,d.[2] AS Date2
    ,r.[2] AS Rate2
    ,d.[3] AS Date3
    ,r.[3] AS Rate3
FROM dates d
JOIN rates r ON d.Account = r.Account

EDIT:

Here's an updated version of the query that pads the pivot column names with leading zeros to create a three-digit ordinal.

SET @cols = 
STUFF((
        SELECT DISTINCT
            ',' + QUOTENAME(RIGHT(('00'+CONVERT(VARCHAR(30), DENSE_RANK() OVER ( PARTITION BY a.account ORDER BY a.ratechangedate ))),3))
        FROM MARS_DW.dbo.vw_GTMScheduledRateAndPaymentChangesWithAccountNumber_Archive a
        INNER JOIN
        (
            SELECT account
            FROM mars..vw_loans
            WHERE loanstatus <> 'bk payment plan'
        ) b ON a.account = b.account
        WHERE
            a.ArchiveDate = '5/21/2019'
            AND a.AppliedDate > '1/2/2018'
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, ''
)

SET @colsAll = 
STUFF((
        SELECT DISTINCT
            ',' + 'd.' + QUOTENAME(RIGHT(('00'+CONVERT(VARCHAR(30), DENSE_RANK() OVER ( PARTITION BY a.account ORDER BY a.ratechangedate ))),3)) + ' AS [Date'
            + RIGHT(('00'+CONVERT(VARCHAR(30), DENSE_RANK() OVER ( PARTITION BY a.account ORDER BY a.ratechangedate ))),3) + '], ' + 'r.'
            + QUOTENAME(RIGHT(('00'+CONVERT(VARCHAR(30), DENSE_RANK() OVER ( PARTITION BY a.account ORDER BY a.ratechangedate ))),3)) + ' AS [Rate'
            + RIGHT(('00'+CONVERT(VARCHAR(30), DENSE_RANK() OVER ( PARTITION BY a.account ORDER BY a.ratechangedate ))),3) + ']'
        FROM MARS_DW.dbo.vw_GTMScheduledRateAndPaymentChangesWithAccountNumber_Archive a
        INNER JOIN
        (
            SELECT account
            FROM mars..vw_loans
            WHERE loanstatus <> 'bk payment plan'
        ) b ON a.account = b.account
        WHERE
            a.ArchiveDate = '5/21/2019'
            AND a.AppliedDate > '1/2/2018'
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, ''
)


SET @query = ';WITH dates as (SELECT Account, ' + @cols
             + ' from 
            (
                select a.Account
                    , a.ratechangedate
                    , DateIndex = RIGHT((''00''+CONVERT(VARCHAR(30), DENSE_RANK() OVER ( PARTITION BY a.account ORDER BY a.ratechangedate ))),3)
                from  MARS_DW.dbo.vw_GTMScheduledRateAndPaymentChangesWithAccountNumber_Archive a
                INNER JOIN (SELECT account
                                FROM mars..vw_loans
                                WHERE loanstatus <> ''bk payment plan''
                            ) b ON a.account = b.account
                WHERE a.ArchiveDate = ''5/21/2019'' AND a.AppliedDate > ''1/2/2018''
           ) x

            pivot
            (
                min(ratechangedate)
                for DateIndex in (' + @cols + ')
            ) d)


            ,rates as (SELECT Account, ' + @cols
             + ' from 
            (
                select a.Account
                    , a.new_noterate
                    , RateIndex = RIGHT((''00''+CONVERT(VARCHAR(30), DENSE_RANK() OVER ( PARTITION BY a.account ORDER BY a.ratechangedate ))),3)
                from  MARS_DW.dbo.vw_GTMScheduledRateAndPaymentChangesWithAccountNumber_Archive a
                INNER JOIN (SELECT account
                                FROM mars..vw_loans
                                WHERE loanstatus <> ''bk payment plan''
                            ) b ON a.account = b.account
                WHERE a.ArchiveDate = ''5/21/2019'' AND a.AppliedDate > ''1/2/2018''
           ) x

            pivot
            (
                min(new_noterate)
                for RateIndex in (' + @cols + ')
            ) r)


            SELECT d.Account, ' + @colsAll + '
            FROM dates d
            JOIN rates r ON d.Account = r.Account'

Thank you very much, I noticed when I remove the a.AppliedDate > '1/2/2018' the ordering gets a bit messed up. It goes from Date 1, Rate 1 to Date 10 Rate 10... any idea how to get the ordering correct? — , May 30 '19 at 15:45
The easiest would be to pad the `DENSE_RANK()` values with enough leading zeros so that varchar column names get sorted as expected. So everywhere that `CONVERT(VARCHAR(30), DENSE_RANK() OVER ( PARTITION BY a.account ORDER BY a.ratechangedate))` appears, change it to `('00'+CONVERT(VARCHAR(30), DENSE_RANK() OVER ( PARTITION BY a.account ORDER BY a.ratechangedate)))`. This will give you column names like [Date001] ... [Date010] etc. — digital.aaron, May 30 '19 at 15:59
Could you possibly edit your code, I tried to make that change but it didn't work it gave me errors — , May 30 '19 at 16:03
I was able to get it to run but it still goes from date001 rate001 to date0010 rate0010 etc... — , May 30 '19 at 16:09
@Snorrlaxxx Check out the edit. I forgot to tell you to `RIGHT()` the resultant string to the appropriate number of digits. So for a three-digit identifier, you'd add `'00'` to the beginning of the number and then `RIGHT(,3)`. If you need four digits as in `[Date0001]`, you'd instead add `'000'` to the beginning of the number and then `RIGHT(,4)`. — digital.aaron, May 30 '19 at 16:53

Making a dynamic pivot table but without using the fiddle

2 Answers2

EDIT: