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
3
votes
1 answer

STRING_AGG replacement in SQL Server 2016

I would like to aggregate single column values with an separator in between and with some prefix. Below is the code tried which works. SELECT concat('TNB/IAG/',STRING_AGG(WF_ValStr, '/')) AS Result FROM wfattrdata where wf_id=35262472 and …
eshaa
  • 386
  • 2
  • 7
  • 26
3
votes
4 answers

Postgresql put strings inside quotes with array_to_string

In select I have used array_to_string like this (example) array_to_string(array_agg(tag_name),';') tag_names I got resulting string "tag1;tag2;tag3;..." but I would like to get resulting string as "'tag1';'tag2';'tag3';...". How can I do this in…
Developer Marius Žilėnas
  • 5,813
  • 1
  • 41
  • 70
2
votes
1 answer

Aggregate character string into vector in R

I have a data table test: id key 1 2365 1 2365 1 3709 2 6734 2 1908 2 4523 I want to aggregate unique key values by id into vector using data.table package. Expected output: id key_array 1 "2365", "3709" 2 "6734",…
Hilary
  • 475
  • 3
  • 10
2
votes
2 answers

How to do this in SQL query?

Table: A Table: B Table: C ------------ ---------------- ------------- P_id | G_id P_id | Name G_id | Title ------------ ---------------- …
Gagan
  • 21
  • 2
2
votes
3 answers

SQL convert column to comma separated row

I am trying to combine UserName field into a comma separated string if the Name is the same. Current output: Since Name = Admin has 4 users linked to it, I am trying to display as as admin@insi.co.za, miguel.reece8@gmail.com, mmm@test.com,…
mig_08
  • 161
  • 1
  • 3
  • 11
2
votes
2 answers

PL/SQL equivalent of T-SQL set-oriented variable concatenation

In Microsoft SQL Server (T-SQL), there is a succinct, elegant, and performant set-oriented way to populate a VARCHAR variable with a concatenation of values from multiple rows in a table, i.e.: DECLARE @vals NVARCHAR(MAX) SELECT @vals = ISNULL(@vals…
Cade Bryant
  • 737
  • 2
  • 7
  • 19
2
votes
1 answer

SQL Concatenate all values in column B that have same value in column A for all values in column A

I am running PostgreSQL 12.4. I have a relatively large table like the following where column 1 and 2 are both of character varying type: |---------------------|------------------| | Column 1 | Column 2 …
Xela
  • 71
  • 8
2
votes
1 answer

Finding Error when running STRING_AGG function

I would like to ask about a script in BigQuery. So, I tried to use a query below SELECT id, STRING_AGG(DISTINCT status, ', ' ORDER BY timestamp) AS grouping FROM table GROUP BY id But I couldn't run it since it gave me an error An aggregate…
Iren Ramadhan
  • 187
  • 1
  • 12
2
votes
3 answers

SQL Server Concatenate three different columns into a Comma-Separated without repeated values

The next table is a simplification of my problem in SQL Server: ID COLUMN_A COLUMN_B COLUMN_C ------------------------------------- 1 A B C 1 A B D 1 B C D I want to get…
alejoaldana
  • 86
  • 6
2
votes
2 answers

How to use a column as a key to pivot columns in SQL

I am trying to pivot a table and aggregate some sub-categories. However, when aggregating, the sub-categories aren't joining properly. Example Table | category | categorySub | |----------|-------------| | cat-1 | sub-1 | | cat-1 | sub-2 …
Matthew
  • 1,461
  • 3
  • 23
  • 49
2
votes
1 answer

Corresponding GROUP_CONCAT() in SQL SERVER 2017

I want to convert the following MYSQL query to MS SQL Server. But, I am sure that GROUP_CONCAT doesn't exist in MS SQL Server prior to 2017. There is a function in SQL SERVER 2017. Can anyone help me? SELECT region, GROUP_CONCAT(route_name) AS…
Rubin Anbin
  • 188
  • 2
  • 13
2
votes
3 answers

Postgres group by empty string question to include empty string in output

I have following table in Postgres | phone | group | spec | | 1 | 1 | 'Lock' | | 1 | 2 | 'Full' | | 1 | 3 | 'Face' | | 2 | 1 | 'Lock' | | 2 | 3 | 'Face' | | 3 | 2 | 'Scan' | Tried…
Yalav
  • 31
  • 4
2
votes
2 answers

How to concatenate rows into one cell and add break line after each one using SQL Server

I am using SQL Server 2017 and I am trying to create a query that concatenates the languages and Levels of Proficiency in one line for every Employee. The table that stores the info in my SQL Database is this for example: And the end result I…
Kate10
  • 53
  • 6
2
votes
3 answers

STRING_AGG ignores GROUP BY in PostgreSQL

I have prepared an SQL Fiddle for my question - In a 2-player word game I store players and their games in the 2 tables: CREATE TABLE players ( uid SERIAL PRIMARY KEY, name text NOT NULL ); CREATE TABLE games ( gid SERIAL PRIMARY KEY, …
Alexander Farber
  • 21,519
  • 75
  • 241
  • 416
2
votes
1 answer

Constructing a string out of several records with 2 columns

I have prepared a simple SQL Fiddle for my question - In a word game written in Pl/pgSQL for PostgreSQL 10.2 player moves are stored in the table: CREATE TABLE words_scores ( mid bigint NOT NULL REFERENCES words_moves ON DELETE…
Alexander Farber
  • 21,519
  • 75
  • 241
  • 416
1 2 3
…
25 26