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
473
votes
14 answers

How to concatenate strings of a string field in a PostgreSQL 'group by' query?

I am looking for a way to concatenate the strings of a field within a group by query. So for example, I have a table: ID COMPANY_ID EMPLOYEE 1 1 Anna 2 1 Bill 3 2 Carol 4 2 Dave and I wanted to group by company_id to get something…
Guy C
  • 6,970
  • 5
  • 30
  • 30
371
votes
12 answers

Simulating group_concat MySQL function in Microsoft SQL Server 2005?

I'm trying to migrate a MySQL-based app over to Microsoft SQL Server 2005 (not by choice, but that's life). In the original app, we used almost entirely ANSI-SQL compliant statements, with one significant exception -- we used MySQL's group_concat…
DanM
  • 7,037
  • 11
  • 51
  • 86
345
votes
9 answers

Postgresql GROUP_CONCAT equivalent?

I have a table and I'd like to pull one row per id with field values concatenated. In my table, for example, I have this: TM67 | 4 | 32556 TM67 | 9 | 98200 TM67 | 72 | 22300 TM99 | 2 | 23009 TM99 | 3 | 11200 And I'd like to output: TM67 |…
TwixxyKit
  • 9,953
  • 9
  • 31
  • 32
210
votes
10 answers

SQL Query to concatenate column values from multiple rows in Oracle

Would it be possible to construct SQL to concatenate column values from multiple rows? The following is an example: Table A PID A B C Table B PID SEQ Desc A 1 Have A 2 a nice A 3 day. B 1 Nice Work. C …
jagamot
  • 5,348
  • 18
  • 59
  • 96
180
votes
4 answers

How to sort the result from string_agg()

I have a table: CREATE TABLE tblproducts ( productid integer, product character varying(20) ) With the rows: INSERT INTO tblproducts(productid, product) VALUES (1, 'CANDID POWDER 50 GM'); INSERT INTO tblproducts(productid, product) VALUES (2,…
Vivek S.
  • 19,945
  • 7
  • 68
  • 85
135
votes
4 answers

How to make a query with group_concat in sql server

I know that in sql server we cannot use Group_concat function but here is one issue i have in which i need to Group_Concat my query.I google it found some logic but not able to correct it.My sql query is select …
Rahul
  • 5,603
  • 6
  • 34
  • 57
121
votes
11 answers

How can I combine multiple rows into a comma-delimited list in Oracle?

I have a simple query: select * from countries with the following results: country_name ------------ Albania Andorra Antigua ..... I would like to return the results in one row, so like this: Albania, Andorra, Antigua, ... Of course, I can write…
rics
  • 5,494
  • 5
  • 33
  • 42
75
votes
4 answers

How can multiple rows be concatenated into one in Oracle without creating a stored procedure?

How can I achieve the following in oracle without creating a stored procedure? Data Set: question_id element_id 1 7 1 8 2 9 3 10 3 11 3 12 Desired Result: question_id …
Dan Polites
  • 6,750
  • 10
  • 50
  • 56
63
votes
4 answers

Produce DISTINCT values in STRING_AGG

I'm using the STRING_AGG function in SQL Server 2017. I'd like to create the same effect as COUNT(DISTINCT ). I tried STRING_AGG(DISTINCT ,',') but that is not legal syntax. I'd like to know if there is a T-SQL work-around. Here…
Brent Arias
  • 29,277
  • 40
  • 133
  • 234
62
votes
11 answers

How do I create a comma-separated list using a SQL query?

I have 3 tables called: Applications (id, name) Resources (id, name) ApplicationsResources (id, app_id, resource_id) I want to show on a GUI a table of all resource names. In one cell in each row I would like to list out all of the applications…
leora
  • 188,729
  • 360
  • 878
  • 1,366
53
votes
8 answers

Get unique values using STRING_AGG in SQL Server

The following query returns the results shown below: SELECT ProjectID, newID.value FROM [dbo].[Data] WITH(NOLOCK) CROSS APPLY STRING_SPLIT([bID],';') AS newID WHERE newID.value IN ('O95833', 'Q96NY7-2') Results: ProjectID …
gkoul
  • 1,067
  • 1
  • 10
  • 19
42
votes
2 answers

What's the equivalent for LISTAGG (Oracle database) in PostgreSQL?

I have to replace the Oracle driver with the newest PostgreSQL. PostgreSQL doesn't know the function LISTAGG. I have to concat values by comma separated. What's the equivalent for the Oracle's function LISTAGG in PostgreSQL?
cy221
  • 1,029
  • 3
  • 12
  • 24
31
votes
2 answers

String_agg for SQL Server before 2017

Can anyone help me make this query work for SQL Server 2014? This is working on Postgresql and probably on SQL Server 2017. On Oracle it is listagg instead of string_agg. Here is the SQL: select string_agg(t.id,',') AS id from Table t I…
xMilos
  • 1,519
  • 4
  • 21
  • 36
24
votes
2 answers

String Aggregation in sqlite

Anyone knows if String Aggregation in sqlite is possible? If i have an animal column with 5 rows/datas, how can i combine them so that the output would be in one field 'dog','cat','rat','mice','mouse' as animals Thanks
monmonja
  • 2,203
  • 5
  • 22
  • 33
20
votes
2 answers

STRING_AGG aggregation result exceeded the limit of 8000 bytes error

I need to combine texts by group. I found a function called STRING_AGG. select c.id , c.bereichsname , STRING_AGG(j.oberbereich,',') oberBereiches from stellenangebote_archiv as j join bereiche as c on j.bereich_id = c.id group by c.id,…
Eren G.
  • 455
  • 1
  • 5
  • 14
1
2 3
…
25 26