Создание динамической сводной таблицы, но без использования скрипки

Я сделал это:

;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

Что дает мне это

Но это не динамично, и моя версия Microsoft 2016 не позволяет мне использовать фиддл, рекомендованный в разных постах. Таким образом, мне рекомендовали использовать функцию Coalesce(), но я понятия не имею, что сделать, чтобы сделать последнюю динамической. Любая помощь (не относящаяся к какому-то сообщению) действительно поможет.

Обновлять:

После комментария я попробовал это:

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 '

но получить эту ошибку:

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'.

Обновлять:

Я пробовал это, чтобы ограничить количество

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)

Но я получаю эту ошибку:

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

Как и просили, вот 10 лучших из таблицы данных.

Создание динамической сводной таблицы, но без использования скрипки

Чтобы сделать ваш свод динамическим, вы должны использовать динамический sql. Fiddle обычно является ссылкой на dbfiddle.uk. У @Taryn--DBA в SO-- есть отличный ответ в эта почта. Что вы не понимаете в этом?

— 29.05.2019 21:26

Вы использовали Account вместо ratechangedate для заполнения @cols.

— 29.05.2019 23:14

Вместо того, чтобы использовать execute(@query) в качестве последнего утверждения, используйте print(@query) и найдите свою синтаксическую ошибку.

— 29.05.2019 23:58

Синтаксическая ошибка вызвана отсутствием псевдонима для вашего второго предложения PIVOT. Однако, если вы добавите к нему псевдоним, вы получите новую ошибку о том, что имена столбцов во втором PIVOT уже указаны в первом PIVOT (поскольку вы используете одну и ту же переменную @cols для создания имен сводных столбцов). Было бы действительно очень полезно, если бы вы могли предоставить определение таблицы vw_GTMScheduledRateAndPaymentChangesWithAccountNumber_Archiv‌e (т. е. имена столбцов, типы данных, несколько строк необработанных данных).

— 30.05.2019 00:26

@digital.aaron digital.aaron Я совершенно новичок в SQL, что вы подразумеваете под определением таблицы? Большинство типов данных — это числа с плавающей запятой, даты и varchars.

— 30.05.2019 00:45

Это будет оператор CREATE TABLE, используемый для создания таблицы. Если вы не знаете, как это получить, это не проблема. Запустите этот запрос и сообщите о результатах: SELECT TOP 10 * FROM MARS_DW.dbo.vw_GTMScheduledRateAndPaymentChangesWithAccountN‌umber_Archive

— 30.05.2019 01:02

Кроме того, что именно вы пытаетесь сделать с этим запросом? Это что-то вроде «Я хочу получить первую дату изменения скорости и новую_ноту для каждой учетной записи»? Я просто хочу убедиться, что вы не создаете для себя проблему XY (см. объяснение в xyproblem.info).

— 30.05.2019 01:06

@digital.aaron Пожалуйста, смотрите редактирование, пришлось скрыть некоторые конфиденциальные данные.

— 30.05.2019 01:44

sql sql-server

29.05.2019 21:22

ReactJs | Supabase | Добавление данных в базу данных

Это и есть ваш редактор таблиц в supabase.👇

Понимание Python и переход к SQL

Перед нами лабораторная работа по BloodOath:

Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Ответ принят как подходящий

Вам было бы намного проще создать перекрестный запрос. Если вы можете определить шаблон, динамический код может быть проще кодировать, и в Интернете (и на этом сайте) есть множество примеров. Если вы не знаете, как создавать динамический код, я бы посоветовал вам держаться подальше от этого, пока вы полностью не поймете, что можно и чего нельзя делать.

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;

Обновлено:

Я сказал вам прочитать статью о том, как работать с динамическим SQL. Я также сказал вам определить шаблон, вы этого не сделали. Теперь есть ответ, который будет намного медленнее, чем то, что я предлагаю, и я просто не хочу, чтобы некачественный код распространялся, так что вот вариант.

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;

У меня нет выбора, чтобы держаться подальше, если бы я это сделал, я бы полностью держался подальше от SQL...

— 29.05.2019 21:33

Затем вы обязаны узнать, как обращаться с динамическим SQL. Хорошее начало sommarskog.se/dynamic_sql.html

— 29.05.2019 21:41

Можете ли вы просто показать мне, как это сделать, чтобы я мог вернуться к своей жизни

— 29.05.2019 21:42

Я мог бы, но я не хочу лишать тебя возможности учиться. Если вы сделаете это неправильно, вы можете столкнуться с проблемой Бобби Столы.

— 29.05.2019 21:48

Из того, что я сделал выше в редактировании обновления, что я делаю неправильно?

— 29.05.2019 21:49

Здесь действительно не помешала бы помощь

— 29.05.2019 21:57

Ошибка говорит The number of elements in the select list exceeds the maximum allowed number of 4096 elements, что означает, что точка поворота слишком широка, и SQL Server не сможет ее обработать. У вас более 2000 изменений на аккаунт? Возможно, вы захотите ограничить это.

— 29.05.2019 23:12

Пожалуйста, смотрите правки, если вы знаете, как это сделать, я думаю, что я достаточно старался, чтобы компенсировать лишение в обучении, действительно мог бы использовать ответ.

— 29.05.2019 23:52

Мой «некачественный» код действительно работает и дает набор результатов, который ищет OP. Ваше решение создает пару столбцов ([date xx] и [rate xx]) для каждого отдельного значения ratechangedate. В итоге вы получите огромную, очень малонаселенную таблицу. Мое решение находит все «первые» изменения скорости в [дата 1] и [ставка 1], независимо от того, когда эти даты на самом деле.

— 30.05.2019 18:25

Вы принимаете это очень личное. Вы действительно должны учиться, а не голосовать против людей, которые говорят, что ваш код не оптимален. Мало того, что ваш код возвращает столбцы в неправильном порядке, он также как минимум в два раза медленнее, чем альтернатива перекрестной таблицы, и имеет вдвое больше чтений. Ваш код также имеет дурную привычку встраивать параметры в строку вместо использования параметризованных запросов с sp_executesql. Так что да, ваш код некачественный.

— 30.05.2019 20:15

@ digital.aaron Я должен с вами согласиться, поскольку ваш код действительно дает правильный результат. Не обижайся, Луис, но твой код даже близко не дает ожидаемого результата.

— 30.05.2019 21:05

Привет, Луис, мне интересно, знаете ли вы, как заставить sql игнорировать эту ошибку: Предупреждение: нулевое значение устраняется агрегатной или другой операцией SET. Когда я помещаю этот запрос в python, он жалуется. Я попытался выполнить SET ANSI_WARNINGS OFF, но затем я получаю эту ошибку: Msg 1934, уровень 16, состояние 1, строка 34 SELECT не удалось, поскольку следующие параметры SET имеют неверные настройки: «ANSI_WARNINGS». Убедитесь, что параметры SET подходят для использования с индексированными представлениями и/или индексами вычисляемых столбцов, и/или отфильтрованными индексами, и/или уведомлениями о запросах, и/или методами типа данных XML, и/или операциями пространственного индекса.

— 23.07.2019 00:37

Во-первых, вы можете добавить ELSE в CASE. Если вы все еще можете получить NULL, добавьте ISNULL() со значением, которое можно легко игнорировать в зависимости от вашей агрегации. Для MIN вы используете верхний предел типа данных, для MAX — нижний предел, для SUM вы добавляете 0 и т. д.

— 23.07.2019 15:27

29.05.2019 21:32

Поигравшись с временной версией вашей таблицы, я думаю, что наконец понял это. Однако сначала следует отметить несколько вещей.

Поскольку вы пытаетесь повернуть два столбца, вам нужно повернуть их по отдельности, а затем снова соединить подрезультаты.
Поскольку мы собираемся использовать @cols для обеих сводок, нам нужно создать версию переменной @cols, которая использует псевдонимы динамически созданных имен столбцов сводки для окончательного выбора.

Итак, давайте углубимся в код.

Во-первых, мы создадим строки, которые будут содержать имена наших динамически созданных столбцов.

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, ''
)

При настройке @colsAll я использую псевдонимы таблиц, которые будут определены при окончательном выборе.

А теперь, чтобы сгенерировать пивот:

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 )

Если бы вы PRINT выполнили запрос, вы бы увидели запрос, который будет выполнен. Мне всегда нравится PRINT запрос, а затем копировать и вставлять результат в новое окно запроса и запускать его. Если всплывают какие-либо ошибки, проще отлаживать окончательный запрос, чем пытаться отлаживать динамический sql. Для справки, запрос, который выдает приведенный выше код, выглядит так:

;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

Обновлено:

Вот обновленная версия запроса, который дополняет имена сводных столбцов ведущими нулями для создания трехзначного порядкового номера.

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'

Большое спасибо, я заметил, что когда я удаляю a.AppliedDate> '1/2/2018', порядок немного искажается. Он идет от даты 1, рейтинг 1 до даты 10, рейтинг 10 ... есть идеи, как правильно упорядочить?

— 30.05.2019 17:45

Проще всего было бы дополнить значения DENSE_RANK() достаточным количеством начальных нулей, чтобы имена столбцов varchar сортировались должным образом. Поэтому везде, где появляется CONVERT(VARCHAR(30), DENSE_RANK() OVER ( PARTITION BY a.account ORDER BY a.ratechangedate)), замените его на ('00'+CONVERT(VARCHAR(30), DENSE_RANK() OVER ( PARTITION BY a.account ORDER BY a.ratechangedate))). Это даст вам имена столбцов, такие как [Date001] ... [Date010] и т. д.

— 30.05.2019 17:59

Не могли бы вы отредактировать свой код, я пытался внести это изменение, но это не сработало, выдало мне ошибки

— 30.05.2019 18:03

Я смог запустить его, но он все еще идет от date001 rate001 до date0010 rate0010 и т. д.

— 30.05.2019 18:09

@Snorrlaxxx Проверьте редактирование. Я забыл сказать вам RIGHT() результирующую строку с соответствующим количеством цифр. Таким образом, для трехзначного идентификатора вы должны добавить '00' в начало номера, а затем RIGHT(<expr>,3). Если вам нужны четыре цифры, как в [Date0001], вместо этого вы должны добавить '000' в начало числа, а затем RIGHT(<expr>,4).

— 30.05.2019 18:53

30.05.2019 02:56