Stack Overflow
Stack Overflow

Questions tagged [string-aggregation]

This tag is intended for SQL questions where multiple string (character) values should be aggregated into a single value using GROUP BY. The equivalent of sum() - just for strings.

The ISO/IEC SQL standard function for this is LISTAGG, but several DBMS:es have their own string aggregation functions:

386 questions
18
votes
1 answer

STRING_AGG not behaving as expected

I have the following query: WITH cteCountryLanguageMapping AS ( SELECT * FROM ( VALUES ('Spain', 'English'), ('Spain', 'Spanish'), ('Sweden', 'English'), ('Switzerland', 'English'), …
Tom Hunter
  • 5,714
  • 10
  • 51
  • 76
13
votes
2 answers

redshift - how to use listagg with information_schema.columns table

I'm using redshift and would like to create a comma separated list of columns. I'm trying to grab the column names from information schema using listagg: SELECT listagg(column_name,',') within group (order by ordinal_position) FROM …
noober
  • 1,427
  • 3
  • 23
  • 36
12
votes
1 answer

String_agg in sql server 2016

Here is my code in sql server 2016 insert into @entdef_queries(entitydefid,squery) select A.entitydefid , ( select String_agg(cols,ioperator) from …
shane
  • 127
  • 1
  • 1
  • 6
12
votes
5 answers

CONCAT(column) OVER(PARTITION BY...)? Group-concatentating rows without grouping the result itself

I need a way to make a concatenation of all rows (per group) in a kind of window function like how you can do COUNT(*) OVER(PARTITION BY...) and the aggregate count of all rows per group will repeat across each particular group. I need something…
Zane Bien
  • 22,685
  • 6
  • 45
  • 57
11
votes
4 answers

STRING_AGG with line break

DROP TABLE IF EXISTS items; CREATE TABLE items (item varchar(20)); INSERT INTO items VALUES ('apple'),('raspberry'); SELECT STRING_AGG(item, CHAR(13)) AS item_list FROM items; How do I get a line break between items ?
Ludovic Aubert
  • 9,534
  • 4
  • 16
  • 28
10
votes
4 answers

Oracle: Combine multiple results in a subquery into a single comma-separated value

I'm trying to convert a single-columned subquery into a command-separated VARCHAR-typed list of values. This is identical to this question, but for Oracle rather than SQL Server or MySQL.
Jason Cohen
  • 81,399
  • 26
  • 107
  • 114
9
votes
1 answer

Equivalent function to STUFF in SQL (GROUP_CONCAT in MySSQL / LISTAGG in Oracle)

Does anyone know if Firebird 2.5 has a function similar to the "STUFF" function in SQL? I have a table which contains parent user records, and another table which contains child user records related to the parent. I'd like to be able to pull a…
Phil
  • 4,029
  • 9
  • 62
  • 107
8
votes
3 answers

'stuff' and 'for xml path('')' from SQL Server in Postgresql

I'm migrating some SQL Server 2008R2 queries to Postgresql 9.0 and I have some trouble with it. Here's the SQL Server query: stuff((select ', '+p.[NAME] as 'data()' from BPROVIDERS_PROVIDER p, BORDER_ARTICLEORDERPROVIDER aop where p.OID =…
user1891262
  • 91
  • 1
  • 1
  • 3
5
votes
1 answer

how to concatenate strings?

I'm on Oracle 10g and have the following table structure: id, paragraph I want to group by id and concatenate the paragraphs. Each paragraph maybe 1500 characters or more. When I try the wm_concat function, it complains that the string buffer is too…
firebird
  • 3,461
  • 6
  • 34
  • 45
5
votes
3 answers

How to avoid duplicates in the STRING_AGG function

My query is below: select u.Id, STRING_AGG(sf.Naziv, ', ') as 'Ustrojstvena jedinica', ISNULL(CONVERT(varchar(200), (STRING_AGG(TRIM(p.Naziv), ', ')), 121), '') as 'Partner', from Ugovor as u left join VezaUgovorPartner…
user7512602
5
votes
2 answers

Create a User defined function like SQL server 2017 STRING_AGG on earlier versions

I try to create a generic function that can be used like this example of using the new string_agg built-in function on SQL Server 2017 the inside implementation can be something like the follow with tbl as( select a.Id, c.Desc from TableA…
oCcSking
  • 888
  • 21
  • 43
5
votes
4 answers

Why does the wm_concat not work here?

I have this query : (SELECT OBJECT_ID from cr_object_group_entries_vw where object_group_id IN (SELECT ITEM FROM TABLE(CR_FN_SPLIT_STRING('28,56',',')))) that returns : But when I do : SELECT wm_concat(object_id) FROM (SELECT OBJECT_ID…
sprocket12
  • 5,368
  • 18
  • 64
  • 133
5
votes
4 answers

Oracle concatenation of columns with comma

Possible Duplicate: How can I combine multiple rows into a comma-delimited list in Oracle? Could some one please tell me how to achieve the following? Table: efforts_id cycle_name release_name 123 quarter march…
Jap Evans
  • 1,097
  • 8
  • 22
  • 42
4
votes
2 answers

Difference between string_agg vs array_agg in PostgreSQL?

I think string_agg vs array_agg is almost same when one is return string type and another is return array type. Is there any other difference between them? Which should I prefer? First or second? array_agg(tag_name, ',') as…
Suman Kumar Dash
  • 681
  • 5
  • 19
4
votes
3 answers

Query to get multiple row into single row

I have a table in which following information are there: ITEM WH BATCH DOC NO CLD1 FN B1 3 CLD1 FN B1 3 CLD1 FN B2 3 CLD1 FN B2 3 CLD1 FN B3 3 CLD1 FN B4 3 This is the code which I have used to…
DeePak
  • 135
  • 1
  • 9
1
2
3
…
25 26