Перенести неопределенное количество строк в столбцы?
У меня есть две таблицы с данными, назовем их таблицей T и таблицей B. Соответствующие данные в T и B имеют отношение «многие к одному» друг с другом, например:
| T.id | B.код |
|---|---|
| А | 1 |
| А | 1,2 |
| Б | 1,2 |
| Б | 1,5 |
| С | 1 |
| С | 2 |
| С | 3 |
| С | 15 |
| Д | 296 |
и т. д. (это всего лишь грубая демонстрация и не похожа на фактические данные, которые я использую).
Я выполнил запрос count(*) и обнаружил, что наибольшее количество элементов из таблицы T появляется в таблице B равно 8. Я хочу транспонировать приведенную выше таблицу, которая имеет тысячи и тысячи записей в T и несколько сотни возможных записей для соответствующего значения B в таблице из 9 столбцов, например:
| T.id | КОД 1 | КОД 2 | КОД 3 | КОД 4 | КОД 5 | КОД 6 | КОД 7 | КОД 8 |
|---|---|---|---|---|---|---|---|---|
| А | 1 | 1,2 | НУЛЕВОЙ | НУЛЕВОЙ | НУЛЕВОЙ | НУЛЕВОЙ | НУЛЕВОЙ | НУЛЕВОЙ |
| Б | 1,2 | 1,5 | НУЛЕВОЙ | НУЛЕВОЙ | НУЛЕВОЙ | НУЛЕВОЙ | НУЛЕВОЙ | НУЛЕВОЙ |
| С | 1 | 2 | 3 | 15 | НУЛЕВОЙ | НУЛЕВОЙ | НУЛЕВОЙ | НУЛЕВОЙ |
| Д | 296 | НУЛЕВОЙ | НУЛЕВОЙ | НУЛЕВОЙ | НУЛЕВОЙ | НУЛЕВОЙ | НУЛЕВОЙ | НУЛЕВОЙ |
и т. д.
Единственная информация о поиске, которую я могу найти, требует либо ручного присоединения B каждый раз и исключения предыдущих значений B.code (как я сейчас это делаю), либо использования таблицы PIVOT (динамический SQL или иначе), которая обязательно будет иметь один столбец для каждого возможного значения B.code (которое, как уже упоминалось, составляет несколько сотен значений), ни одно из которых не является масштабируемым.
Для справки, мой код сейчас выглядит примерно так:
SELECT
T.id,
b1.code as 'CODE 1', b2.code as 'CODE 2', b3.code as 'CODE 3', b4.code as 'CODE 4' (etc)
FROM T
LEFT JOIN B b1 ON b1.code =
(SELECT TOP 1 code FROM B WHERE B.t_id = T.id)
LEFT JOIN B b2 ON b2.code =
(SELECT TOP 1 code FROM B WHERE B.t_id = T.id AND B.code NOT IN (b1.code))
LEFT JOIN B b3 ON b3.code =
(SELECT TOP 1 code FROM B WHERE B.t_id = T.id AND B.code NOT IN (b1.code, b2.code))
LEFT JOIN B b4 ON b4.code =
(SELECT TOP 1 code FROM B WHERE B.t_id = T.id AND B.code NOT IN (b1.code, b2.code, b3.code))
Каковы мои варианты? Я обречен? Должен быть лучший способ, верно?
Вам нужен правильный CR/LF между началом/концом уценки таблицы и другим текстом, который не является частью таблицы. etc. пытался стать частью таблицы, но структура не совпадала, поэтому бум.
Пожалуйста, предоставьте определение таблицы для B и несколько строк примера данных. Ваш код выглядит так же, как и динамический поворот, но более сложным способом. Есть ли коды без значений?
Таблица T вообще актуальна? Если тот же код/ключ присутствует в B, нужно ли вам присоединяться? Или вы можете присоединиться к после, к которому вы изменились?
@ Kendle Я не уверен, насколько это актуально; единственные столбцы, на которые я когда-либо ссылался в таблице B, — это два, перечисленные в первой таблице примера (хотя вместо T.id это был бы B.t_id). Но есть еще два столбца: Seq (который является собственным столбцом с уникальным идентификатором) и Lead (который равен либо 1, либо NULL). @ AaronBertrand Я могу присоединиться после поворота, так как это не имеет прямого отношения к самому повороту. Я получаю оттуда данные в виде открытого текста вместо уникального идентификатора.
И является ли Code числом, строкой или чем-то еще? Как вы определяете, что 1 принадлежит Code 1, а 1.2 принадлежит Code 2?
Code — это строка. Коды заменяются по порядку и не имеют приоритета, поэтому, хотя они отсортированы внутри в таблице B, мне этого достаточно.
Ответы 2
Не проверено, но, возможно, это поможет
Select *
From (
Select T.id
,B.Code
,Col = concat('Code ',row_number() over (partition by T.id order by B.code) )
From T
Join B on B.ID=T.ID
) src
Pivot (max(Code) for Col in ([Code 1],
[Code 2],
[Code 3],
[Code 4],
[Code 5],
[Code 6],
[Code 7],
[Code 8]) ) pvt
@GabrielBenamy Всегда рад помочь
@GabrielBenamy Должен отметить: если есть дублирующиеся коды, и вы хотите показать только разные значения (скажем, у A есть два кода = 1), используйте плотно_rank() вместо row_number()
Запрос, который вы хотите использовать для завершить:
;WITH x AS
(
SELECT [T.id], Code, rn = ROW_NUMBER() OVER
(PARTITION BY [T.id] ORDER BY @@SPID)
FROM dbo.B
) SELECT [T.id], [CODE 1] = p.[1],[CODE 2] = p.[2], ...
FROM x
PIVOT (MAX(Code) FOR rn IN
([1],[2],...)) AS p;
Но вам нужен динамический SQL, чтобы добраться туда без необходимости жестко кодировать ...Code 8 или добавлять Code 9, когда в таблицу добавляется другой код:
DECLARE @sql nvarchar(max) = N'',
@outputColumns nvarchar(max),
@pivotColumns nvarchar(max),
@maxCount int = (SELECT MAX(c) FROM
(SELECT COUNT(*) FROM dbo.B GROUP BY [T.id]) AS c(c));
;WITH x(n) AS (SELECT 1 UNION ALL SELECT n+1
FROM x WHERE n < @maxCount)
SELECT @outputColumns = STRING_AGG(CONCAT('[CODE ',n,
'] = p.',QUOTENAME(n)), ','),
@pivotColumns = STRING_AGG(QUOTENAME(n), N',')
FROM x;
SELECT @sql = N';WITH x AS
(
SELECT [T.id], Code, rn = ROW_NUMBER() OVER
(PARTITION BY [T.id] ORDER BY @@SPID)
FROM dbo.B
) SELECT [T.id], ' + @outputColumns + N'
FROM x
PIVOT (MAX(Code) FOR rn IN
(' + @pivotColumns + N')) AS p;';
EXEC sys.sp_executesql @sql;
Вывод (из примера данных в вопросе):
T.id CODE 1 CODE 2 CODE 3 CODE 4 A 1 1.2 null null B 1.2 1.5 null null C 1 2 3 15 D 296 null null null
- Пример дб <> рабочий пример
По какой-то причине таблицы не отображаются здесь должным образом, но они отображаются, когда я нажимаю «Изменить». Как я могу это исправить? ? Обновлено: Спасибо, Аарон!