Как переместить значения столбцов вперед, если предыдущее значение находится в списке исключенных значений?

Question

У меня есть некоторый код TSQL, который создает ненормализованный плоский файл из хорошо организованных реляционных таблиц. Код завершается быстро, и данные не перегружены, поэтому есть вероятность, что любые предложения помогут. Мне не нужно сильно беспокоиться о производительности, потому что этот процесс предназначен для запуска только 1 раз в месяц. У меня есть комната для маневра в этом отношении.

Исходные данные, например, ради, выложены так: у одного человека (таблица 1) может быть много инцидентов (таблица 2). К каждому инциденту может быть привязано много кодов (таблица 3). Каждый код имеет упорядоченную последовательность. Таким образом, после выравнивания одна строка в файле экстракта может выглядеть так:

Name                IncidentId  Code1   Code2   Code3   Code4
Sue Ellen Crandell  1991        abc1    def1    xyz0    888

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

Name                IncidentId  Code1   Code2   Code3   Code4
Sue Ellen Crandell  1991        abc1    xyz0    888     <empty string>

Прежде чем я получу дополнительные реляционные данные и экспортирую результаты в файл, я использую динамический T-SQL, чтобы нормализовать эти упорядоченные значения кода во временной таблице. Из-за нежелания связываться с динамическим T-SQL и возможных ограничений возможности использования условных выражений для смещения столбцов во время этой части процесса, я думаю, что самое простое место для размещения оценки списка исключений было бы после упорядоченные значения кода превращают его в временную таблицу.

Если у меня есть временная таблица, которая выглядит как первая строка данных выше, как я могу

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

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

Answer 1

Сначала создайте CTE, который разворачивает таблицу так, чтобы каждый код находился в отдельной строке:

with cte(Name, IncidentId, CodeName, Code)
as(
    select Name, IncidentId, CodeName, Code
    from Incident i 
    unpivot(Code for CodeName in (Code1, Code2, Code3, Code4)) unpvt
)

Теперь вы делаете внешнее соединение на CTE с самим собой, отфильтровывая исключенные коды. Это дает вам одну строку для каждого кортежа Name-Incident-Code, но у вас есть нулевые значения в строках, где код был исключен (вам нужны нулевые строки для поддержания правильного количества кодов).

Select *, t1.Name, t1.IncidentId, isnull(t2.Code, '') Code, 
ROW_NUMBER() over(partition by t1.Name, t1.IncidentId order by isnull(t2.CodeName, 'zzz')) CodeNumber
    from cte t1
    left outer join cte t2 on t1.Name = t2.Name and 
           t1.IncidentId = t2.IncidentId and 
           t1.Code = t2.Code and 
           not exists(select 1 from Exclude e where e.Code = t2.Code)

ROW_NUMBER() здесь создаст новый CodeNumber. order byisnull(t2.CodeNumber, 'zzz')) толкает пустые строки до конца, так что строки, имеющие допустимые коды, нумеруются первыми (потому что "zzz" больше, чем "Code-what-").

Теперь вам просто нужно повернуть предыдущий запрос, чтобы коды снова стали столбцами:

select Name, IncidentId, [1] Code1, [2] Code2, [3] as Code3, [4] as Code4
from
(
    Select t1.Name, t1.IncidentId, isnull(t2.Code, '') Code, ROW_NUMBER() over(partition by t1.Name, t1.IncidentId order by isnull(t2.CodeName, 'zzz')) CodeNumber
    from cte t1
    left outer join cte t2 on t1.Name = t2.Name and t1.IncidentId = t2.IncidentId and t1.Code = t2.Code and not exists(select 1 from Exclude e where e.Code = t2.Code)
) x
pivot(max(Code) for CodeNumber in ([1], [2], [3], [4])
) as pvt

SQL Fiddle

Обновить

Есть пара проблем с кодом выше. Во-первых, когда я создаю CodeNumber с помощью ROW_NUMBER(), я сортирую по CodeName. Это разбивается после 9 столбцов кода, потому что они больше не сортируются правильно (они сортируются по алфавиту, а не по номерам). Поэтому мне нужно вытащить кодовый номер в CTE, чтобы я мог использовать его для сортировки позже:

with cte(Name, IncidentId, CodeName, CodeNumber, Code)
as(
    select Name, IncidentId, CodeName, convert(int, SUBSTRING(CodeName, 5, len(CodeName))), Code
    from Incident i 
    unpivot(Code for CodeName in (Code1, Code2, Code3, Code4, Code5, Code6, Code7, Code8, Code9, Code10)) unpvt
)

Теперь остальная часть запроса выглядит так:

select Name, IncidentId, [1] Code1, [2] Code2, [3] as Code3, [4] as Code4, [5] as Code5, [6] as Code6, [7] as Code7, [8] as Code8, [9] as Code9, [10] as Code10
from
(
    Select t1.Name, t1.IncidentId, isnull(t2.Code, '') Code, ROW_NUMBER() over(partition by t1.Name, t1.IncidentId order by isnull(t2.CodeNumber, 999)) NewCodeNumber
    from cte t1
    left outer join cte t2 on t1.Name = t2.Name and t1.IncidentId = t2.IncidentId and t1.Code = t2.Code and not exists(select 1 from Exclude e where e.Code = t2.Code)
) x
pivot(max(Code) for NewCodeNumber in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10])
) as pvt

Обратите внимание, что поскольку у меня теперь есть столбец с именем CodeNumber в CTE, я вызываю вновь созданный номер "NewCodeNumber". Кроме того, я заказываю по t2.CodeNumber вместо t1.Code,

Обновлена SQL Fiddle.

Обновить

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

with cteCode(Name, IncidentId, CodeName, CodeNumber, Code)
as(
    select Name, IncidentId, CodeName, convert(int, SUBSTRING(CodeName, 5, len(CodeName))), Code
    from Incident i 
    unpivot(Code for CodeName in (Code1, Code2, Code3, Code4, Code5, Code6, Code7, Code8, Code9, Code10)) unpvt
), cteCodeDate(Name, IncidentId, CodeName, CodeNumber, CodeDate)
as(
    select Name, IncidentId, CodeName, convert(int, SUBSTRING(CodeName, 9, len(CodeName))), CodeDate
    from Incident i 
    unpivot(CodeDate for CodeName in (CodeDate1, CodeDate2, CodeDate3, CodeDate4, CodeDate5, CodeDate6, CodeDate7, CodeDate8, CodeDate9, CodeDate10)) unpvt
)

а затем присоединиться к ним вместе:

Select t1.Name, t1.IncidentId, isnull(t2.Code, '') Code, ROW_NUMBER() over(partition by t1.Name, t1.IncidentId order by isnull(t2.CodeNumber, 999)) NewCodeNumber, t3.CodeDate
    from cteCode t1
    join cteCodeDate t3 on t3.Name = t1.Name and t3.IncidentId = t1.IncidentId and t3.CodeNumber = t1.CodeNumber
    left outer join cteCode t2 on t1.Name = t2.Name and t1.IncidentId = t2.IncidentId and t1.Code = t2.Code and not exists(select 1 from Exclude e where e.Code = t2.Code)

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

select Name, IncidentId,
MAX(case when newCodeNumber = 1 then Code end) Code1,
MAX(case when newCodeNumber = 1 then CodeDate end) CodeDate1,
MAX(case when newCodeNumber = 2 then Code end) Code2,
MAX(case when newCodeNumber = 2 then CodeDate end) CodeDate2,
MAX(case when newCodeNumber = 3 then Code end) Code3,
MAX(case when newCodeNumber = 3 then CodeDate end) CodeDate3,
MAX(case when newCodeNumber = 4 then Code end) Code4,
MAX(case when newCodeNumber = 4 then CodeDate end) CodeDate4,
MAX(case when newCodeNumber = 5 then Code end) Code5,
MAX(case when newCodeNumber = 5 then CodeDate end) CodeDate5,
MAX(case when newCodeNumber = 6 then Code end) Code6,
MAX(case when newCodeNumber = 6 then CodeDate end) CodeDate6,
MAX(case when newCodeNumber = 7 then Code end) Code7,
MAX(case when newCodeNumber = 7 then CodeDate end) CodeDate7,
MAX(case when newCodeNumber = 8 then Code end) Code8,
MAX(case when newCodeNumber = 8 then CodeDate end) CodeDate8,
MAX(case when newCodeNumber = 9 then Code end) Code9,
MAX(case when newCodeNumber = 9 then CodeDate end) CodeDate9,
MAX(case when newCodeNumber = 10 then Code end) Code10,
MAX(case when newCodeNumber = 10 then CodeDate end) CodeDate10
from
(
    Select t1.Name, t1.IncidentId, isnull(t2.Code, '') Code, ROW_NUMBER() over(partition by t1.Name, t1.IncidentId order by isnull(t2.CodeNumber, 999)) NewCodeNumber, t3.CodeDate
    from cteCode t1
    join cteCodeDate t3 on t3.Name = t1.Name and t3.IncidentId = t1.IncidentId and t3.CodeNumber = t1.CodeNumber
    left outer join cteCode t2 on t1.Name = t2.Name and t1.IncidentId = t2.IncidentId and t1.Code = t2.Code and not exists(select 1 from Exclude e where e.Code = t2.Code)
) x
group by Name, IncidentId

SQL Fiddle

Answer 2

Это слишком долго для комментария.

Это лучше всего обрабатывается с помощью динамического SQL. Перемещение объектов из столбца в столбец для обработки исключений в лучшем случае громоздко. В конечном итоге это будет вариант:

if code1 is not excluded then code1
   else if code2 is not excluded then code2
   else if code3 is not excluded then code3
   else code4 is not excluded then code4 as code1
if code1 is not excluded 
   if code2 is not excluded then code2
   else if code3 is not excluded then code3
   else if code4 is not excluded then code4
and so on, and so on and so on

Вместо этого у вас, вероятно, есть место, где вы можете добавить что-то вроде этого в динамический SQL:

where not exists (select 1 from ExcludedCodes ec where ec.code <> the.code)

И вы уничтожите их до опоры.