[Oracle] Можно ли объединить несколько значений одного столбца

ifani

Допустим, есть такая таблица:
col1 col2
1 aa
2 aa
2 bb
3 cc
3 dd
Хочется получить на выходе такое множество:
1 aa
2 aa, bb
3 cc, dd
Сделать это с помощью хранимой процедуры на PL/SQL никакой сложности не представляет. А можно ли тут как-то обойтись только лишь запросом - ведь, по сути, это GROUP BY плюс некая аггрегирующая функция?

Boris1980

некая аггрегирующая функция = хранимая функция конкатенации
Чем не подходит?

ifani

Ну, тут две причины:
1. Некоторое время назад обратил внимание, что некоторые вещи пишу на PL/SQL только лишь потому, что на нём проще и привычнее выразить свои мысли, а не потому, что это лучше с точки зрения эффективности запроса. Имхо, это неправильно. Поэтому с тех пор стараюсь сначала попробовать решить проблему средствами самого SQL, что зачастую получается, действительно, эффективнее. Можно сказать, спортивный интерес

2. Это часть большого скрипта, который предполагается отдать человеку, который прогонит его у себя на сервере.
А так придётся ещё дополнительно пакет создавать. Но вообще, видимо, так и сделаю, так как гугл никаких готовых подходящих функций не подсказал.

Boris1980

Насчет именно конкатенации, где-то на АскТом был вопрос, там как раз хранимую функцию и посоветовали.

ifani

Уже как раз там этот вопрос нашёл.
Так что успокоился и пишу хранимую функцию

PS:
Всё-таки странно, что для этого нет стандартной функции - например, судя по инету, в MySQL для этого есть GROUP_CONCAT.

katrin2201

Но вообще, видимо, так и сделаю, так как гугл никаких готовых подходящих функций не подсказал.

STARTS WITH/CONNECT BY разве не подойдет?
другое дело, что с точки зрения эффективности вряд ли получится лучше твоей хранимой процедуры

ifani

Такой вариант вместе с использованием SYS_CONNECT_BY_PATH я тоже рассматривал (собственно, как раз из-за SYS_CONNECT_BY_PATH я и предположил, что, может, есть готовые аггрегирующие функци конкатенации для строк но подумал, что использовать тут иерархические запросы - уже верх извращения :grin:

А в итоге, сделал по старинке через написание своей функции.
PS:
Хотя видел ещё более извратный вариант через использовние ораклового пакета для построения xml запросов :grin:

vovaV09

1 aa
2 aa, bb
3

стандартный PIVOT с последующей конкатенацией ( см., например, http://www.sql.ru/faq/faq_topic.aspx?fid=210 )

ifani

Что-то мне кажется, что это не то (в смысле, что предложенная реализация не подходит) - у меня не известно количество различных значений ни в первом, ни во втором столбце.
Но за термин спасибо - не знал, что это так называется.

vovaV09

если Oracle11g, то там появился оператор PIVOT, иначе можно с помощью PL/SQL сгенерировать нужный запрос.
Например,

 

   PROCEDURE create_pivot_report_p (

      in_pivot_table          IN   VARCHAR2,

      in_pivot_column         IN   VARCHAR2,

      in_group_by_column      IN   VARCHAR2,

      in_aggregate_function   IN   VARCHAR2 DEFAULT 'COUNT',

      in_aggregate_column     IN   VARCHAR2,

      in_view                 IN   VARCHAR2

   )

   AS

      in_pivot_column_values   budjet_util_pkg.array_varchar;

      create_view              VARCHAR2 (2000);

   BEGIN

      -- первая часть создания представления

      create_view :=

            'CREATE OR REPLACE VIEW '

         || in_view

         || ' AS SELECT '

         || in_group_by_column;



      -- выбираем все различные значения столбца, по кот. нам нужно "повернуть" данные

      EXECUTE IMMEDIATE    'SELECT DISTINCT '

                        || in_pivot_column

                        || ' FROM '

                        || in_pivot_table

                        || ' ORDER BY '

                        || in_pivot_column

      BULK COLLECT INTO in_pivot_column_values;





      -- вторая часть создания представления

      FOR i IN in_pivot_column_values.FIRST .. in_pivot_column_values.LAST

      LOOP

         create_view :=

               create_view

            || ','

            || in_aggregate_function

            || '(CASE '

            || in_pivot_column

            || ' WHEN '''

            || in_pivot_column_values (i)

            || ''' THEN '

            || in_aggregate_column

            || ' ELSE 0 END) AS "'

            || SUBSTR(in_pivot_column_values (i1,30)

            || '"';

      END LOOP;



      -- третья часть создания представления

      create_view :=

            create_view

         || ','

         || in_aggregate_function

         || '('

         || in_aggregate_column

         || ') AS '

         || '"'

         || UPPER (in_aggregate_function)

         || '_OF_'

         || UPPER (in_aggregate_column)

         || '"'

         || ' FROM '

         || in_pivot_table

         || ' GROUP BY '

         || in_group_by_column;



      EXECUTE IMMEDIATE create_view;

   END;

Vantucha

в десятке вроде wm_concat есть

pitrik2

некая аггрегирующая функция = хранимая функция конкатенации

ток. надо бы оракл 10 минимум использовать
до десятки жти агрегирующие фиговые

хотя вощем-то вполне работает, я так подобный конкат и делал когда-то

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