23май Как колонку таблицы записать в строку?

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

Предлагаю следующий метод с использованием иерархических запросов и функции SYS_CONNECT_BY_PATH. Если у вас есть лучший способ, то предложите. Всегда рады сотрудничеству.

Есть такая таблица:

create table dbst_tab (id number(2))

в ней такие строчки

ID
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

выполним такой запрос

select id,lag(id) over (order by id ) as id_1 from dbst_TAB

получим такой результат

ID|ID_1
1|null
2|1
3|2
4|3
5|4
6|5
7|6
8|7
9|8
10|9
11|10
12|11
13|12
14|13
15|14
16|15
17|16
18|17
19|18
20|19

а теперь

select sys_connect_by_path(id, ',') as "ID"
   from ( select id,lag(id) over (order by id ) as id_1 from dbst_TAB)
   start with id_1 is null
   connect by id_1 = prior id

результат будет таковым

ID
,1
,1,2
,1,2,3
,1,2,3,4
,1,2,3,4,5
,1,2,3,4,5,6
,1,2,3,4,5,6,7
,1,2,3,4,5,6,7,8
,1,2,3,4,5,6,7,8,9
,1,2,3,4,5,6,7,8,9,10
,1,2,3,4,5,6,7,8,9,10,11
,1,2,3,4,5,6,7,8,9,10,11,12
,1,2,3,4,5,6,7,8,9,10,11,12,13
,1,2,3,4,5,6,7,8,9,10,11,12,13,14
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20

Заберем ненужный значок

select ltrim(sys_connect_by_path(id, ','),',') as "ID"
   from ( select id,lag(id) over (order by id ) as id_1 from dbst_TAB)
   start with id_1 is null
   connect by id_1 = prior id

ID
1
1,2
1,2,3
1,2,3,4
1,2,3,4,5
1,2,3,4,5,6
1,2,3,4,5,6,7
1,2,3,4,5,6,7,8
1,2,3,4,5,6,7,8,9
1,2,3,4,5,6,7,8,9,10
1,2,3,4,5,6,7,8,9,10,11
1,2,3,4,5,6,7,8,9,10,11,12
1,2,3,4,5,6,7,8,9,10,11,12,13
1,2,3,4,5,6,7,8,9,10,11,12,13,14
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20

осталось только взять последнюю строку. Сделаем это так

select * from (select ltrim(sys_connect_by_path(id, ','),',') as "ID"
      from ( select id,lag(id) over (order by id ) as id_1 from dbst_TAB)
      start with id_1 is null
      connect by id_1 = prior id
      order by 1 desc)
      where rownum=1

получаем строку

ID
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20

это и есть ответ на заданный вопрос. А теперь возьмём немного сложнее табличку и выполним все по аналогии:

CREATE TABLE DBST_TABL
 (CITY    VARCHAR2(20 BYTE),
  NAME    VARCHAR2(30 BYTE),
  PERIOD  VARCHAR2(6 BYTE),
  GOODS   VARCHAR2(6 BYTE));
insert into  dbst_tabl (city,name,period,goods) values ('c1','n1','200601','t0');
insert into  dbst_tabl (city,name,period,goods) values ('c1','n1','200601','t1');
insert into  dbst_tabl (city,name,period,goods) values ('c1','n1','200601','t2');
insert into  dbst_tabl (city,name,period,goods) values ('c1','n1','200601','t3');
insert into  dbst_tabl (city,name,period,goods) values ('c1','n1','200602','t1');
insert into  dbst_tabl (city,name,period,goods) values ('c1','n1','200602','t2');
insert into  dbst_tabl (city,name,period,goods) values ('c1','n1','200603','t3');
insert into  dbst_tabl (city,name,period,goods) values ('c1','n1','200603','t4');
insert into  dbst_tabl (city,name,period,goods) values ('c1','n1','200603','t5');
insert into  dbst_tabl (city,name,period,goods) values ('c1','n2','200601','t0');
insert into  dbst_tabl (city,name,period,goods) values ('c1','n2','200601','t2');
insert into  dbst_tabl (city,name,period,goods) values ('c1','n2','200601','t3');
insert into  dbst_tabl (city,name,period,goods) values ('c1','n2','200601','t4');
insert into  dbst_tabl (city,name,period,goods) values ('c1','n4','200603','t3');
insert into  dbst_tabl (city,name,period,goods) values ('c1','n4','200603','t4');
insert into  dbst_tabl (city,name,period,goods) values ('c1','n4','200603','t5');
insert into  dbst_tabl (city,name,period,goods) values ('c1','n4','200604','t1');
insert into  dbst_tabl (city,name,period,goods) values ('c1','n4','200604','t2');
insert into  dbst_tabl (city,name,period,goods) values ('c1','n4','200604','t3');
insert into  dbst_tabl (city,name,period,goods) values ('c2','n2','200601','t0');
insert into  dbst_tabl (city,name,period,goods) values ('c2','n2','200601','t2');
insert into  dbst_tabl (city,name,period,goods) values ('c2','n2','200601','t3');
insert into  dbst_tabl (city,name,period,goods) values ('c2','n2','200601','t4');
insert into  dbst_tabl (city,name,period,goods) values ('c2','n3','200601','t5');
insert into  dbst_tabl (city,name,period,goods) values ('c2','n3','200602','t1');
insert into  dbst_tabl (city,name,period,goods) values ('c2','n3','200602','t2');
insert into  dbst_tabl (city,name,period,goods) values ('c2','n4','200603','t3');
insert into  dbst_tabl (city,name,period,goods) values ('c3','n4','200603','t4');
insert into  dbst_tabl (city,name,period,goods) values ('c3','n4','200603','t5');
insert into  dbst_tabl (city,name,period,goods) values ('c3','n4','200604','t1');
insert into  dbst_tabl (city,name,period,goods) values ('c3','n4','200604','t2');

начнем с такого запроса

select city,name, period,goods formm,
lag(goods) over (partition by city,name,period order by goods ) as form_prev 
from dbst_tabl

получим результат

CITY|NAME|PERIOD|FORMM|FORM_PREV
c1|n1|200601|t0|null|
c1|n1|200601|t1|t0|
c1|n1|200601|t2|t1|
c1|n1|200601|t3|t2|
c1|n1|200602|t1|null|
c1|n1|200602|t2|t1|
c1|n1|200603|t3|null|
c1|n1|200603|t4|t3|
c1|n1|200603|t5|t4|
c1|n2|200601|t0|null|
c1|n2|200601|t2|t0|
c1|n2|200601|t3|t2|
c1|n2|200601|t4|t3|
c1|n4|200603|t3|null|
c1|n4|200603|t4|t3|
c1|n4|200603|t5|t4|
c1|n4|200604|t1|null|
c1|n4|200604|t2|t1|
c1|n4|200604|t3|t2|
c2|n2|200601|t0|null|
c2|n2|200601|t2|t0|
c2|n2|200601|t3|t2|
c2|n2|200601|t4|t3|
c2|n3|200601|t5|null|
c2|n3|200602|t1|null|
c2|n3|200602|t2|t1|
c2|n4|200603|t3|null|
c3|n4|200603|t4|null|
c3|n4|200603|t5|t4|
c3|n4|200604|t1|null|
c3|n4|200604|t2|t1|

select city,name,period,sys_connect_by_path(formm, ',') formms,length(sys_connect_by_path(formm, ','))len
      from 
(  
select city,name, period,goods formm,
lag(goods) over (partition by city,name,period order by goods ) as form_prev 
from dbst_tabl 
)
start with form_prev is null
connect by  city=prior city and name=prior name and period=prior period and form_prev=prior formm

CITY|NAME|PERIOD|FORMMS|LEN
c1|n1|200601|,t0|3|
c1|n1|200601|,t0,t1|6|
c1|n1|200601|,t0,t1,t2|9|
c1|n1|200601|,t0,t1,t2,t3|12|
c1|n1|200602|,t1|3|
c1|n1|200602|,t1,t2|6|
c1|n1|200603|,t3|3|
c1|n1|200603|,t3,t4|6|
c1|n1|200603|,t3,t4,t5|9|
c1|n2|200601|,t0|3|
c1|n2|200601|,t0,t2|6|
c1|n2|200601|,t0,t2,t3|9|
c1|n2|200601|,t0,t2,t3,t4|12|
c1|n4|200603|,t3|3|
c1|n4|200603|,t3,t4|6|
c1|n4|200603|,t3,t4,t5|9|
c1|n4|200604|,t1|3|
c1|n4|200604|,t1,t2|6|
c1|n4|200604|,t1,t2,t3|9|
c2|n2|200601|,t0|3|
c2|n2|200601|,t0,t2|6|
c2|n2|200601|,t0,t2,t3|9|
c2|n2|200601|,t0,t2,t3,t4|12|
c2|n3|200601|,t5|3|
c2|n3|200602|,t1|3|
c2|n3|200602|,t1,t2|6|
c2|n4|200603|,t3|3|
c3|n4|200603|,t4|3|
c3|n4|200603|,t4,t5|6|
c3|n4|200604|,t1|3|
c3|n4|200604|,t1,t2|6|

А теперь осталось выбрать по каждой группе запись с наибольшей длинной поля FORMMS ( т. е. max(len)) Предлагаю сделать так:

select city,name,period,ltrim(formms,',') result from 

     (select city,name,period,sys_connect_by_path(formm, ',') formms,      
      length(sys_connect_by_path(formm, ','))len from 

          (  
          select city,name, period,goods formm,
         lag(goods) over (partition by city,name,period order by goods ) as form_prev 
         from dbst_tabl 
          )

        start with form_prev is null
        connect by  city=prior city and name=prior name and period=prior period 
            and form_prev=prior formm) ll 

where len=(
  
                   select max(len) from 
                   (select city,name,period,sys_connect_by_path(formm, ',')      
                   formms,length(sys_connect_by_path(formm, ','))len
        from (select city,name, period,goods formm,
        lag(goods) over (partition by city,name,period order by goods ) as form_prev 
        from dbst_tabl )
        start with form_prev is null
        connect by  city=prior city and name=prior name and period=prior period and 
                   form_prev=prior formm) jj where jj.city=ll.city and jj.name=ll.name 
                   and jj.period=ll.period  group by city,name,period)
					  
order by 1,2,3

CITY|NAME|PERIOD|RESULT
c1|n1|200601|t0,t1,t2,t3|
c1|n1|200602|t1,t2|
c1|n1|200603|t3,t4,t5|
c1|n2|200601|t0,t2,t3,t4|
c1|n4|200603|t3,t4,t5|
c1|n4|200604|t1,t2,t3|
c2|n2|200601|t0,t2,t3,t4|
c2|n3|200601|t5|
c2|n3|200602|t1,t2|
c2|n4|200603|t3|
c3|n4|200603|t4,t5|
c3|n4|200604|t1,t2|

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

Построим иерархический запрос:
select level,formm from
(select city,name, period,goods formm,
lag(goods) over (partition by city,name,period order by goods ) as form_prev 
from dbst_tabl)
start with form_prev is null
connect by form_prev=prior formm and city=prior city and name= prior name and period= prior period
LEVEL|FORMM
1|t0|
2|t1|
3|t2|
4|t3|
1|t1|
2|t2|
1|t3|
2|t4|
3|t5|
1|t0|
2|t2|
3|t3|
4|t4|
1|t3|
2|t4|
3|t5|
1|t1|
2|t2|
3|t3|
1|t0|
2|t2|
3|t3|
4|t4|
1|t5|
1|t1|
2|t2|
1|t3|
1|t4|
2|t5|
1|t1|
2|t2|

Таким образом, создали иерархию. А теперь иерархия должна быть отображена в строке. Для этого есть функция SYS_CONNECT_BY_PATH.

SYS_CONNECT_BY_PATH - это функция для иерархических запросов. Возвращает путь значений колонки от корня к узлу (каждое значения колонки разделено указанным разделителем), полученных по условию CONNECT BY. Тип колонки только CHAR, VARCHAR2, NCHAR, or NVARCHAR2. Возвращаемая строка типа VARCHAR2 .

Это делается запросом

(select level,sys_CONNECT_BY_PATH(formm,',') from
(select city,name, period,goods formm,
lag(goods) over (partition by city,name,period order by goods ) as form_prev 
from dbst_tabl)

start with form_prev is null
connect by form_prev=prior formm and city=prior city and name= prior name and period= prior period)

LEVEL|SYS_CONNECT_BY_PATH(FORMM,',')
1|,t0|
2|,t0,t1|
3|,t0,t1,t2|
4|,t0,t1,t2,t3|
1|,t1|
2|,t1,t2|
1|,t3|
2|,t3,t4|
3|,t3,t4,t5|
1|,t0|
2|,t0,t2|
3|,t0,t2,t3|
4|,t0,t2,t3,t4|
1|,t3|
2|,t3,t4|
3|,t3,t4,t5|
1|,t1|
2|,t1,t2|
3|,t1,t2,t3|
1|,t0|
2|,t0,t2|
3|,t0,t2,t3|
4|,t0,t2,t3,t4|
1|,t5|
1|,t1|
2|,t1,t2|
1|,t3|
1|,t4|
2|,t4,t5|
1|,t1|
2|,t1,t2|

Немного теории

Иерархические запросы в Oracle обеспечиваются фразой CONNECT в операторе SELECT:

• START WITH - задает строку/строки, лежащие в корне иерархии. Эта фраза определяет условие, которому должны соответствовать корневые строки. Условие не должно содержать подзапросов. Если эта фраза не задана, то все строки таблицы являются корневыми.
• CONNECT BY - задает отношение между родительскими и дочерними строками в иерархии. Отношение задается условием, это может быть любое условие, но какая-то его часть должна содержать оператор PRIOR, относящийся к родительской строке:

Чтобы найти дочерние строки Oracle вычисляет PRIOR-выражение для родительской строки, а другое выражение - для каждой строки таблицы. Строки, для которых это выражение дает истину, являются дочерними. CONNECT BY может содержать и другие условия-фильтры. CONNECT BY не может содержать подзапросов.

Выборки Oracle, использующие иерархические свойства запросов, могут использовать псевдостолбец level. Этот псевдостолбец имеет значение 1 для узла дерева, находящегося в корне, 2 - для узлов, являющихся непосредственными потомками корневого, и т.д.

В Oracle Database 10g для запросов с предложением CONNECT BY появилась целая группа новых функций, таких, как:

• CONNECT_BY_ROOT – возвращает корень иерархии текущей строки CONNECT BY, эта функция значительно упрощает наш запрос. (Пример см. ниже.);
• CONNECT_BY_ISLEAF – признак, указывающий, что текущая строка имеет дочерние строки;
• CONNECT_BY_ISCYCLE – признак, указывающий, что в вашей иерархии текущая строка является началом бесконечного цикла. Например, если A – родитель B, B – родитель C, а C – родитель A, то у вас будет бесконечный цикл. Вы можете использовать этот признак для определения, какая строка или строки ваших данных являются началом бесконечного цикла;
• NOCYCLE – позволяет в запросе с предложением CONNECT BY распознать, что встретился бесконечный цикл и прекратить выполнение запроса без выдачи ошибки (вместо возврата ошибки зацикливания при выполнении предложения CONNECT BY).

У Вас есть также возможность почитать про аналитические функции (over) в моей статье. Успехов! Если будут замечания, предложения – прошу.