Можно ли построить SQL для конкатенации значений столбцов из несколько строк?
Ниже приведен пример:
Таблица A
PID A B C
Таблица B
PID SEQ Desc A 1 Have A 2 a nice A 3 day. B 1 Nice Work. C 1 Yes C 2 we can C 3 do C 4 this work!
Вывод SQL должен быть -
PID Desc A Have a nice day. B Nice Work. C Yes we can do this work!
Таким образом, в основном столбец Desc для таблицы out put представляет собой конкатенацию значений SEQ из таблицы B?
Любая помощь с SQL?
Есть несколько способов в зависимости от того, какая версия у вас есть - см. Документацию оракула по методам агрегации строк. Очень распространенным является использование LISTAGG
:
SELECT pid, LISTAGG(Desc, ' ') WITHIN GROUP (ORDER BY seq) AS description
FROM B GROUP BY pid;
Затем присоединитесь к A
чтобы выбрать pids
вам pids
.
Примечание: Из коробки, LISTAGG
работает корректно только с VARCHAR2
колоннами.
Также существует функция XMLAGG
, которая работает с версиями до 11.2. Поскольку WM_CONCAT
недокументирован и не поддерживается Oracle, рекомендуется не использовать его в производственной системе.
С помощью XMLAGG
вы можете сделать следующее:
SELECT XMLAGG(XMLELEMENT(E,ename||',')).EXTRACT('//text()') "Result"
FROM employee_names
Что это значит, это
ename
(объединенные запятой) из таблицы employee_names
в элементе xml (с тегом E)С предложением модели SQL:
SQL> select pid
2 , ltrim(sentence) sentence
3 from ( select pid
4 , seq
5 , sentence
6 from b
7 model
8 partition by (pid)
9 dimension by (seq)
10 measures (descr,cast(null as varchar2(100)) as sentence)
11 ( sentence[any] order by seq desc
12 = descr[cv()] || ' ' || sentence[cv()+1]
13 )
14 )
15 where seq = 1
16 /
P SENTENCE
- ---------------------------------------------------------------------------
A Have a nice day
B Nice Work.
C Yes we can do this work!
3 rows selected.
Я написал об этом здесь. И если вы перейдете по ссылке на OTN-нить, вы найдете еще кое-что, включая сравнение производительности.
Аналитическая функция LISTAGG была представлена в Oracle 11g Release 2, что упростило объединение строк. Если вы используете 11g Release 2, вы должны использовать эту функцию для агрегации строк. Пожалуйста, обратитесь к url для получения дополнительной информации о конкатенации строк.
http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php
Как показывает большинство ответов, LISTAGG
является очевидным вариантом. Однако один досадный аспект с LISTAGG
заключается в том, что если общая длина конкатенированной строки превышает 4000 символов (предел для VARCHAR2
в SQL), возникает ошибка ниже, которую трудно управлять в версиях Oracle до 12.1
ORA-01489: результат конкатенации строк слишком длинный
Новая функция, добавленная в 12cR2, - это предложение ON OVERFLOW
LISTAGG
. Запрос, включающий этот раздел, будет выглядеть так:
SELECT pid, LISTAGG(Desc, ' ' on overflow truncate) WITHIN GROUP (ORDER BY seq) AS desc
FROM B GROUP BY pid;
Вышеуказанное ограничивает вывод до 4000 символов, но не ORA-01489
ошибки ORA-01489
.
Вот некоторые из дополнительных опций ON OVERFLOW
:
ON OVERFLOW TRUNCATE 'Contd..'
: в конце строки отобразится 'Contd..'
(по умолчанию - ...
)ON OVERFLOW TRUNCATE ''
: отобразится 4000 символов без какой-либо завершающей строки.ON OVERFLOW TRUNCATE WITH COUNT
: отображает общее количество символов в конце после завершающих символов. Например: - " ...(5512)
"ON OVERFLOW ERROR
: Если вы ожидаете, что LISTAGG
завершится с ошибкой ORA-01489
(по умолчанию это значение по умолчанию).Для тех, кто должен решить эту проблему с использованием Oracle 9i (или ранее), вам, вероятно, потребуется использовать SYS_CONNECT_BY_PATH, поскольку LISTAGG недоступен.
Чтобы ответить на OP, следующий запрос отобразит PID из таблицы A и соедините все столбцы DESC из таблицы B:
SELECT pid, SUBSTR (MAX (SYS_CONNECT_BY_PATH (description, ', ')), 3) all_descriptions
FROM (
SELECT ROW_NUMBER () OVER (PARTITION BY pid ORDER BY pid, seq) rnum, pid, description
FROM (
SELECT a.pid, seq, description
FROM table_a a, table_b b
WHERE a.pid = b.pid(+)
)
)
START WITH rnum = 1
CONNECT BY PRIOR rnum = rnum - 1 AND PRIOR pid = pid
GROUP BY pid
ORDER BY pid;
Также могут быть случаи, когда ключи и значения содержатся в одной таблице. Следующий запрос может быть использован там, где нет таблицы A, и существует только таблица B:
SELECT pid, SUBSTR (MAX (SYS_CONNECT_BY_PATH (description, ', ')), 3) all_descriptions
FROM (
SELECT ROW_NUMBER () OVER (PARTITION BY pid ORDER BY pid, seq) rnum, pid, description
FROM (
SELECT pid, seq, description
FROM table_b
)
)
START WITH rnum = 1
CONNECT BY PRIOR rnum = rnum - 1 AND PRIOR pid = pid
GROUP BY pid
ORDER BY pid;
Все значения могут быть переупорядочены по желанию. Отдельные конкатенированные описания могут быть переупорядочены в предложении PARTITION BY, и список PID может быть переупорядочен в конечном предложении ORDER BY.
Альтернативно: могут быть моменты, когда вы хотите объединить все значения из всей таблицы в одну строку.
Основная идея здесь заключается в использовании искусственного значения для группы описаний, которые должны быть объединены.
В следующем запросе используется константа '1', но любое значение будет работать:
SELECT SUBSTR (MAX (SYS_CONNECT_BY_PATH (description, ', ')), 3) all_descriptions
FROM (
SELECT ROW_NUMBER () OVER (PARTITION BY unique_id ORDER BY pid, seq) rnum, description
FROM (
SELECT '1' unique_id, b.pid, b.seq, b.description
FROM table_b b
)
)
START WITH rnum = 1
CONNECT BY PRIOR rnum = rnum - 1;
Индивидуальные конкатенированные описания могут быть переупорядочены в предложении PARTITION BY.
Несколько других ответов на этой странице также упомянули об этой чрезвычайно полезной ссылке: https://oracle-base.com/articles/misc/string-aggregation-techniques
Прежде чем запускать запрос выбора, запустите это:
SET SERVEROUT ON SIZE 6000
SELECT XMLAGG(XMLELEMENT(E,SUPLR_SUPLR_ID||',')).EXTRACT('//text()') "SUPPLIER"
FROM SUPPLIERS;
11g и выше: используйте listagg:
SELECT
col1,
LISTAGG(col2, ', ') WITHIN GROUP (ORDER BY col2) "names"
FROM table_x
GROUP BY col1
10g и ниже: одним из способов является использование функции:
CREATE OR REPLACE FUNCTION get_comma_separated_value (input_val in number)
RETURN VARCHAR2
IS
return_text VARCHAR2(10000) := NULL;
BEGIN
FOR x IN (SELECT col2 FROM table_name WHERE col1 = input_val) LOOP
return_text := return_text || ',' || x.col2 ;
END LOOP;
RETURN LTRIM(return_text, ',');
END;
/
Чтобы использовать функцию:
select col1, get_comma_separated_value(col1) from table_name
Примечание. Существует функция (неподдерживаемая) WM_CONCAT
доступная в некоторых старых версиях Oracle, которые могут вам помочь - см. Здесь подробности.
В MySQL:
SELECT col1, GROUP_CONCAT(col2) FROM table_name GROUP BY col1
Я использую LISTAGG, но возвращаю эту строку для персидской строки!
мой запрос:
SELECT
listagg(DESCRIPTION,' , ') within group (order by DESCRIPTION)
FROM
B_CEREMONY
результат:
'A7'1 , ,4F
Пожалуйста, помогите мне.
wow это решение работает:
SELECT listagg(convert(DESCRIPTION, 'UTF8', 'AL16UTF16'),' , ') within group
(order by DESCRIPTION)
FROM B_CEREMONY;
LISTAGG обеспечивает лучшую производительность, если сортировка является обязательной (00: 00: 05.85)
SELECT pid, LISTAGG(Desc, ' ') WITHIN GROUP (ORDER BY seq) AS description FROM B GROUP BY pid;
COLLECT обеспечивает наилучшую производительность, если сортировка не требуется (00: 00: 02.90):
SELECT pid, TO_STRING(CAST(COLLECT(Desc) AS varchar2_ntt)) AS Vals FROM B GROUP BY pid;
COLLECT с заказом бит медленнее (00: 00: 07.08):
SELECT pid, TO_STRING(CAST(COLLECT(Desc ORDER BY Desc) AS varchar2_ntt)) AS Vals FROM B GROUP BY pid;
Все остальные методы были медленнее.
В выборе, где вы хотите выполнить конкатенацию, вызовите функцию SQL.
Например:
select PID, dbo.MyConcat(PID)
from TableA;
Затем для функции SQL:
Function MyConcat(@PID varchar(10))
returns varchar(1000)
as
begin
declare @x varchar(1000);
select @x = isnull(@x +',', @x, @x +',') + Desc
from TableB
where PID = @PID;
return @x;
end
Синтаксис заголовка функции может быть неправильным, но принцип действительно работает.
Попробуйте этот код:
SELECT XMLAGG(XMLELEMENT(E,fieldname||',')).EXTRACT('//text()') "FieldNames"
FROM FIELD_MASTER
WHERE FIELD_ID > 10 AND FIELD_AREA != 'NEBRASKA';
Или функция Oracle STRAGG (столбца).
Я должен сказать, что этот вид обработки очень ограничен... если вы превысите ширину поля или ширину экрана...