Преобразование таблицы по уровням

@Wintel · Регистрация: 20.01.2016

Студворк — интернет-сервис помощи студентам

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

id	f_id	lvl
1234	2234	5
1235	2235	5
1236	2236	5
2234	4234	4
2235	3235	4
2236	3236	4
3235	5235	3
5235	6235	1
4234	5234	2
3236	5236	3
5234	6234	1
6234	NULL	0
6235	NULL	0
5236	NULL	1

Её необходимо развернуть и привести к такому виду:

5	4	3	2	1	0
1234	2234	-	4234	5234	6234
1235	2235	3235	-	5235	6235
1236	2236	3236	-	5236	-

Не могу понять, как это сделать? MS SQL 2012, метод Pivot для решения задачи не подошел. Прошу Вашей помощи.

@qwertehok · 28.02.2018, 22:25

Сообщение от Wintel

метод Pivot для решения задачи не подошел

почему?

@Wintel · 28.02.2018, 22:45 **[ТС]**

У него на выходе получается примерно так:

Добавлено через 7 минут

5	4	3	2	1	0
1234	null	null	null	null	null
1235	null	null	null	null	null
1236	null	null	null	null	null
null	2234	null	null	null	null
null	2235	null	null	null	null
null	2236	null	null	null	null
null	null	3235	null	null	null
null	null	3236	null	null	null
null	null	null	4234	null	null
null	null	null	null	5235	null
null	null	null	null	5234	null
null	null	null	null	5236	null
null	null	null	null	null	6234
null	null	null	null	null	6235

Т.е. лишние ячейки, нужно именно в требуемом варианте получить таблицу.

@qwertehok · 01.03.2018, 02:51

У кого - у него?

@kodv · 01.03.2018, 06:41

Сообщение от Wintel

Её необходимо развернуть и привести к такому виду:
5 4 3 2 1 0
1234 2234 - 4234 5234 6234
1235 2235 3235 - 5235 6235
1236 2236 3236 - 5236 -

По какому принципу расставлены знаки '-' в таблице? Почему для уровня 3 '-' стоит в верхней строке, а для уровня 0 в нижней?

@Wintel · 01.03.2018, 09:54 **[ТС]**

Каждый уровень родительского id (f_id) начинается со следубщей цифры(*234, *235, *236), т.к. в столбце id отсутствует 3234, то там будет "-". Аналогично и для нижнего 6 уровня.

@kodv · 01.03.2018, 12:39

Wintel, ну тогда так:

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
WITH MyTable AS
(
    SELECT id, f_id, lvl
        FROM (VALUES (1234, 2234, 5)
                    , (1235, 2235, 5)
                    , (1236, 2236, 5)
                    , (2234, 4234, 4)
                    , (2235, 3235, 4)
                    , (2236, 3236, 4)
                    , (3235, 5235, 3)
                    , (5235, 6235, 1)
                    , (4234, 5234, 2)
                    , (3236, 5236, 3)
                    , (5234, 6234, 1)
                    , (6234, NULL, 0)
                    , (6235, NULL, 0)
                    , (5236, NULL, 1)) T(id, f_id, lvl)
)
SELECT COALESCE([5], N'-') [5], COALESCE([4], N'-') [4], COALESCE([3], N'-') [3]
        , COALESCE([2], N'-') [2], COALESCE([1], N'-') [1], COALESCE([0], N'-') [0]
    FROM (SELECT CONVERT(NCHAR(4), id) id, lvl, STUFF(id, 1, 1, N'') Part
            FROM MyTable) T PIVOT (MAX(id) FOR lvl IN ([5], [4], [3], [2], [1], [0])) T

Заметьте, с PIVOT'ом

@Wintel · 01.03.2018, 22:27 **[ТС]**

Сообщение от kodv

Wintel, ну тогда так:
T-SQLВыделить код

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
WITH MyTable AS
(
* * SELECT id, f_id, lvl
* * * * FROM (VALUES (1234, 2234, 5)
* * * * * * * * * * , (1235, 2235, 5)
* * * * * * * * * * , (1236, 2236, 5)
* * * * * * * * * * , (2234, 4234, 4)
* * * * * * * * * * , (2235, 3235, 4)
* * * * * * * * * * , (2236, 3236, 4)
* * * * * * * * * * , (3235, 5235, 3)
* * * * * * * * * * , (5235, 6235, 1)
* * * * * * * * * * , (4234, 5234, 2)
* * * * * * * * * * , (3236, 5236, 3)
* * * * * * * * * * , (5234, 6234, 1)
* * * * * * * * * * , (6234, NULL, 0)
* * * * * * * * * * , (6235, NULL, 0)
* * * * * * * * * * , (5236, NULL, 1)) T(id, f_id, lvl)
)
SELECT COALESCE([5], N'-') [5], COALESCE([4], N'-') [4], COALESCE([3], N'-') [3]
* * * * , COALESCE([2], N'-') [2], COALESCE([1], N'-') [1], COALESCE([0], N'-') [0]
* * FROM (SELECT CONVERT(NCHAR(4), id) id, lvl, STUFF(id, 1, 1, N'') Part
* * * * * * FROM MyTable) T PIVOT (MAX(id) FOR lvl IN ([5], [4], [3], [2], [1], [0])) T

Заметьте, с PIVOT'ом

Большое спасибо, корректно работает даже если в 5-м левле у id значение NULL, правда лишняя строка появляется, но это не беда

Столкнулся с ситуацией, что в Teradata 14 отсутствует такой метод как Pivot. Есть какие-нибудь мысли по реализации этой же задачи другими методами?

@qwertehok · 01.03.2018, 22:57

Сообщение от Wintel

Есть какие-нибудь мысли по реализации этой же задачи другими методами?

курсором?

@Wintel · 01.03.2018, 23:14 **[ТС]**

Сообщение от qwertehok

Сообщение от Wintel
Есть какие-нибудь мысли по реализации этой же задачи другими методами?
курсором?

А если строк будет >100 000 или > 1 000 000, что по скорости выполнения, сможешь навскидку оценить?

@qwertehok · 02.03.2018, 00:03

ОЧЕНЬ долго

@invm · 02.03.2018, 08:38

Wintel, pivot это синтаксический сахар. Если посмотрите план выполнения, то увидите, что он преобразуется в обычные агрегатные функции.

@kodv · 02.03.2018, 08:58

Сообщение от Wintel

Столкнулся с ситуацией, что в Teradata 14 отсутствует такой метод как Pivot. Есть какие-нибудь мысли по реализации этой же задачи другими методами?

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

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
WITH MyTable AS
(
    SELECT id, f_id, lvl
        FROM (VALUES (1234, 2234, 5)
                    , (1235, 2235, 5)
                    , (1236, 2236, 5)
                    , (2234, 4234, 4)
                    , (2235, 3235, 4)
                    , (2236, 3236, 4)
                    , (3235, 5235, 3)
                    , (5235, 6235, 1)
                    , (4234, 5234, 2)
                    , (3236, 5236, 3)
                    , (5234, 6234, 1)
                    , (6234, NULL, 0)
                    , (6235, NULL, 0)
                    , (5236, NULL, 1)) T(id, f_id, lvl)
)
SELECT COALESCE(MAX(CASE lvl WHEN 5 THEN id END), N'-') [5]
        , COALESCE(MAX(CASE lvl WHEN 4 THEN id END), N'-') [4]
        , COALESCE(MAX(CASE lvl WHEN 3 THEN id END), N'-') [3]
        , COALESCE(MAX(CASE lvl WHEN 2 THEN id END), N'-') [4]
        , COALESCE(MAX(CASE lvl WHEN 1 THEN id END), N'-') [1]
        , COALESCE(MAX(CASE lvl WHEN 0 THEN id END), N'-') [0]
    FROM (SELECT CONVERT(NCHAR(4), id) id, lvl
            FROM MyTable) T
    GROUP BY STUFF(id, 1, 1, N'')

Сообщение от Wintel

если в 5-м левле у id значение NULL, правда лишняя строка появляется, но это не беда

Конечно, появится. В данном запросе строки сгруппированы по id, без учета первого символа (STUFF(id, 1, 1, N'')). Если есть стркои, где id = NULL, то в результирующем наборе будет 4 строки: для 234, для 235, для 236 и для NULL. Думаю, понимая принцип построения запроса, вы сможете без проблем переделать его под свои нужды.

@Wintel · 02.03.2018, 20:13 **[ТС]**

invm, сегодня как раз посмотрел.
kodv, благодарю за столь подробное разъяснение

дальше я сам, ещё раз спасибо.

Новые блоги и статьи Все статьи Все блоги /
SDL3 для Web (WebAssembly): Реализация движения на Box2D v3 - трение и коллизии с повёрнутыми стенами 8Observer8 20.02.2026 Содержание блога Box2D позволяет легко создать главного героя, который не проходит сквозь стены и перемещается с заданным трением о препятствия, которые можно располагать под углом, как верхнее. . .	Конвертировать закладки radiotray-ng в m3u-плейлист damix 19.02.2026 Это можно сделать скриптом для PowerShell. Использование . \СonvertRadiotrayToM3U. ps1 <path_to_bookmarks. json> Рядом с файлом bookmarks. json появится файл bookmarks. m3u с результатом. # Check if. . .	Семь CDC на одном интерфейсе: 5 U[S]ARTов, 1 CAN и 1 SSI Eddy_Em 18.02.2026 Постепенно допиливаю свою "многоинтерфейсную плату". Выглядит вот так: https:/ / www. cyberforum. ru/ blog_attachment. php?attachmentid=11617&stc=1&d=1771445347 Основана на STM32F303RBT6. На борту пять. . .	Камера Toupcam IUA500KMA Eddy_Em 12.02.2026 Т. к. у всяких "хикроботов" слишком уж мелкий пиксель, для подсмотра в ESPriF они вообще плохо годятся: уже 14 величину можно рассмотреть еле-еле лишь на экспозициях под 3 секунды (а то и больше),. . .
И ясному Солнцу zbw 12.02.2026 И ясному Солнцу, и светлой Луне. В мире покоя нет и люди не могут жить в тишине. А жить им немного лет.	«Знание-Сила» zbw 12.02.2026 «Знание-Сила» «Время-Деньги» «Деньги -Пуля»	SDL3 для Web (WebAssembly): Подключение Box2D v3, физика и отрисовка коллайдеров 8Observer8 12.02.2026 Содержание блога Box2D - это библиотека для 2D физики для анимаций и игр. С её помощью можно определять были ли коллизии между конкретными объектами и вызывать обработчики событий столкновения. . . .	SDL3 для Web (WebAssembly): Загрузка PNG с прозрачным фоном с помощью SDL_LoadPNG (без SDL3_image) 8Observer8 11.02.2026 Содержание блога Библиотека SDL3 содержит встроенные инструменты для базовой работы с изображениями - без использования библиотеки SDL3_image. Пошагово создадим проект для загрузки изображения. . .

Преобразование таблицы по уровням

Решение

Решение

@qwertehok 5970 / 4546 / 1094 Регистрация: 29.08.2013 Сообщений: 28,168 Записей в блоге: 3
	01.03.2018, 02:51
	У кого - у него? 0

@Wintel 4 / 4 / 0 Регистрация: 20.01.2016 Сообщений: 26
	01.03.2018, 09:54 [ТС]
	Каждый уровень родительского id (f_id) начинается со следубщей цифры(234, 235, *236), т.к. в столбце id отсутствует 3234, то там будет "-". Аналогично и для нижнего 6 уровня. 0

@qwertehok 5970 / 4546 / 1094 Регистрация: 29.08.2013 Сообщений: 28,168 Записей в блоге: 3
	02.03.2018, 00:03
	ОЧЕНЬ долго 0

@invm 3614 / 2135 / 756 Регистрация: 02.06.2013 Сообщений: 5,169
	02.03.2018, 08:38
	Wintel, pivot это синтаксический сахар. Если посмотрите план выполнения, то увидите, что он преобразуется в обычные агрегатные функции. 0

@Wintel 4 / 4 / 0 Регистрация: 20.01.2016 Сообщений: 26
	02.03.2018, 20:13 [ТС]
	invm, сегодня как раз посмотрел. kodv, благодарю за столь подробное разъяснение дальше я сам, ещё раз спасибо. 0