Добрый день.
Задачка такая:
Есть таблица адресов с полями id, adress, formalname, level, parent_id. Parent_id ссылается на id из этой же таблицы.
id adress formalname level parent_id
1 630123, Новосибирская обл, Новосибирск г, Петухова ул Петухова ул 33 2
2 630123, Новосибирская обл, Новосибирск г Новосибирск г 22 3
3 630123, Новосибирская обл Новосибирская обл 11 null

В итоге должна получиться таблица:
id adress Index 11 22 33
1 630123, Новосибирская обл, Новосибирск г, Петухова ул 630123 Новосбирская обл Новосбирск г Петухова ул

Индекса в поле adress может и не быть.
Получается нужно разбить адрес по уровням level, ссылаясь на родителя.

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

в первом сообщении не понятно как по столбцам разбивается таблица, немного исправила.
Есть таблица адресов с полями id, adress, formalname, level, parent_id. Parent_id ссылается на id из этой же таблицы.
id | adress | formalname | level | parent_id
1 | 630123, Новосибирская обл, Новосибирск г, Петухова ул | Петухова ул | 33 | 2
2 | 630123, Новосибирская обл, Новосибирск г | Новосибирск г | 22 | 3
3 | 630123, Новосибирская обл | Новосибирская обл | 11 | null

В итоге должна получиться таблица:
id | adress | Index | 11 | 22 | 33
1 | 630123, Новосибирская обл, Новосибирск г, Петухова ул | 630123 | Новосбирская обл | Новосбирск г | Петухова ул

Почитала про рекурсивные cte, во всех статьях примеры с выводом дерева в столбик. А мне надо строкой, при этом каждый элемент поместить с столбец в зависимости от уровня level. При этом количество уровней может быть разное. Может быть например 1,2 3 уровень, а может быть 1,3,4 уровень, тогда столбец со вторым уровнем должен быть пустым. Есть какие-то примеры с выводом дерева в строку?

PizzaPizza,

Уровней всего 9. Если родителя какого-то уровня нет, то ячейку надо пропустить. Уровень указан в поле level. Если нет индекса, то ячейка тоже должна остаться пустой.

Руслан Дамирович,

При запуске запроса выдаётся сообщение о том, что cte не существует. Мне кажется, ругается на второй select. У меня используется postgresql, но вряд ли это влияет.

Подскажите, пожалуйста, postgresql не поддерживает квадратные скобки? С ними скрипт не работает. Если их убирать, то тогда как обозначить lvl =1?

Убрала скобки, заменила названия полей и таблиц, в коде ничего не меняла, все равно выдается сообщение о том, что cte не существует (см. файл).

Как тогда правильно написать?

Добрый день.
Подскажите, пожалуйста, все-таки как скрипт нужно исправить, чтобы он заработал

Пишу на Postgressql
Если указать тип поля aolevel integer, то ошибка про несовпадение типов уже не выдается.
Не уверена, что (tt.aolevel = 1)::integer правильно записано, но ошибки уже нет.
Теперь выдается ошибка: function left(character varying, integer) does not exist для строки
MAX( CASE WHEN lvl = 1 AND adr_adm_ter LIKE '[0-9][0-9][0-9][0-9][0-9][0-9],%' THEN LEFT( adr_adm_ter, 6 ) END ) as index,
И ошибка "column "aolevel" does not exist" ругается на последний итоговый select.

Сам запрос:
WITH cte AS (
SELECT
tt.id as base,
(tt.aolevel = 1)::integer as lvl,
tt.adr_adm_ter,
tt.formalname
FROM
edw_prod.edw_ods.t_000148_ent_as_addrobj tt
LEFT JOIN edw_prod.edw_ods.t_000148_ent_as_addrobj tt1 ON (
tt1.parent_id = tt.id )
WHERE
tt1.parent_id IS NULL
UNION ALL
SELECT
tt3.id as base,
(tt3.aolevel + 1)::integer as lvl,
tt.adr_adm_ter,
tt.formalname
FROM
edw_prod.edw_ods.t_000148_ent_as_addrobj tt3
INNER JOIN edw_prod.edw_ods.t_000148_ent_as_addrobj tt ON (
tt.id = tt3.parent_id )
)
SELECT
base,
adr_adm_ter = MAX( CASE WHEN lvl = 1 THEN adr_adm_ter END ),
MAX( CASE WHEN lvl = 1 AND adr_adm_ter LIKE '[0-9][0-9][0-9][0-9][0-9][0-9],%' THEN LEFT( adr_adm_ter, 6 ) END ) as index,
MAX( CASE WHEN aolevel = 1 THEN formalname END ) as formalname1,
MAX( CASE WHEN aolevel = 2 THEN formalname END ) as formalname2,
MAX( CASE WHEN aolevel = 3 THEN formalname END ) as formalname3,
MAX( CASE WHEN aolevel = 4 THEN formalname END ) as formalname4,
MAX( CASE WHEN aolevel = 5 THEN formalname END ) as formalname5,
MAX( CASE WHEN aolevel = 6 THEN formalname END ) as formalname6,
MAX( CASE WHEN aolevel = 7 THEN formalname END ) as formalname7,
MAX( CASE WHEN aolevel = 8 THEN formalname END ) as formalname8,
MAX( CASE WHEN aolevel = 90 THEN formalname END ) as formalname90,
MAX( CASE WHEN aolevel = 91 THEN formalname END ) as formalname91
FROM
cte
GROUP BY
base
limit 1000
;

Согласна, бредятина )) Исправила. Вроде запрос заработал, но выдается какая-то ерунда...
Взять для примера первую строку.
В поле base выдается id улицы (433404, Ульяновская обл, Чердаклинский р-н, Богдашкино с., Полевая ул.), а в поле adr_adm_ter выдается адрес до населенного пункта "433404, Ульяновская обл, Чердаклинский р-н, Богдашкино с."
и в итоге мне надо получить строку с такими значениями (если взять первую строку результатов из файла):
formalname7 = Полевая
formalname6 = Богдашкино
formalname3 = Чердаклинский
formalname1 = Ульяновская
adr_adm_ter = 433404, Ульяновская обл, Чердаклинский р-н, Богдашкино с., Полевая ул.
base = 24292037

Ошибка где-то в первых селектах.