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.

There is no SQL standard function for this, but most DBMS support some kind of string aggregation function:

331 questions
390
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…
Guy C
  • 5,713
  • 4
  • 25
  • 30
353
votes
11 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
  • 6,727
  • 11
  • 48
  • 83
275
votes
7 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
  • 7,577
  • 9
  • 29
  • 31
183
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
  • 4,938
  • 12
  • 55
  • 96
121
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,337
  • 6
  • 30
  • 55
114
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,239
  • 5
  • 30
  • 40
114
votes
3 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.
  • 15,777
  • 6
  • 54
  • 78
67
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,500
  • 10
  • 47
  • 54
57
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
  • 163,579
  • 332
  • 834
  • 1,328
26
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
  • 779
  • 3
  • 10
  • 23
25
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
  • 26,187
  • 32
  • 120
  • 209
24
votes
5 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
  • 716
  • 1
  • 5
  • 12
17
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,195
  • 8
  • 47
  • 74
15
votes
2 answers

string_agg for sql server pre 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 Tabel t I checked on…
Master Yi
  • 815
  • 2
  • 12
  • 28
14
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
  • 1,955
  • 4
  • 19
  • 33
1
2 3
…
22 23