,

Результат Select’а в виде одной строки и одна строка в виде набора строк

четверг, 1 октября 2009 г. Оставить комментарий

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

Вот пару способов это сделать. Примеры выполнены в схеме SCOTT.

SQL> select ltrim(max(sys_connect_by_path(ename, ',')), ',')
2 from (
3 select emp.ename,
4 row_number() over (order by emp.ename) as rn
5 from emp
6 where emp.job = 'SALESMAN'
7 )
8 start with rn = 1
9 connect by prior rn = rn-1;

LTRIM(MAX(SYS_CONNECT_BY_PATH(
--------------------------------------------------------------------------------
ALLEN,MARTIN,TURNER,WARD

SQL>

Еще один способ.

SQL> select rtrim(extract(xmlagg(xmlelement(x, ename||',') order by emp.ename),'//X/text()').getstringval(), ',')
2 from emp
3 where emp.job = 'SALESMAN';

RTRIM(EXTRACT(XMLAGG(XMLELEMEN
--------------------------------------------------------------------------------
ALLEN,MARTIN,TURNER,WARD

SQL>

Еще пару раз приходилось делать наоборот, есть строка, со значениями, и их надо превратить в набор строк со значениями. Ниже несколько способов, как это сделать.


SQL> select emp.empno, emp.ename
2 from emp
3 where emp.ename in
4 (
5 with t as (
6 select ltrim('ALLEN,MARTIN,TURNER,WARD'/*:p_list*/, ',') || ',' as s,
7 rownum as rn
8 from emp /* или all_objects, здесь можно emp*/
9 )
10 select substr(t.s,
11 lag(instr(t.s, ',', 1, rn), 1, 0) over (order by instr(t.s, ',', 1, rn)) + 1,
12 instr(t.s, ',', 1, rn) - lag(instr(t.s, ',', 1, rn), 1, 0) over (order by instr(t.s, ',', 1, rn)) - 1
13 ) as str
14 from t
15 where instr(t.s, ',', 1, rn) != 0
16 );

EMPNO ENAME
----- ----------
7499 ALLEN
7521 WARD
7654 MARTIN
7844 TURNER

SQL>

Еще один способ.


SQL> select emp.empno, emp.ename
2 from emp
3 where emp.ename in
4 (
5 with t as (
6 select '' || replace('ALLEN,MARTIN,TURNER,WARD', ',', '') || '' s
7 from dual
8 )
9 select extractvalue(value(row_data), '/X')
10 from t,
11 table(xmlsequence(extract(xmltype(t.s), '/Y/X'))) row_data
12 );

EMPNO ENAME
----- ----------
7499 ALLEN
7654 MARTIN
7844 TURNER
7521 WARD

SQL>

0 коммент. »

Оставьте Ваш комментарий