Stack Overflow
Stack Overflow

Questions tagged [string-agg]

Concatenates the values of string expressions and places separator values between them. The separator is not added at the end of string.

Applies to

SQL Server 2017 (14.x) and later YesAzure SQL Database YesAzure SQL Managed Instance yesAzure Synapse Analytics.

Syntax

STRING_AGG ( expression, separator ) [ <order_clause> ]

<order_clause> ::=   
    WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] )

Remarks

STRING_AGG is an aggregate function that takes all expressions from rows and concatenates them into a single string. Expression values are implicitly converted to string types and then concatenated. The implicit conversion to strings follows the existing rules for data type conversions. For more information about data type conversions, see CAST and CONVERT (Transact-SQL).

If the input expression is type VARCHAR, the separator cannot be type NVARCHAR.

Null values are ignored and the corresponding separator is not added. To return a place holder for null values, use the ISNULL function as demonstrated in example B.

STRING_AGG is available in any compatibility level.

Official Documentation

73 questions
0
votes
0 answers

Matching data type for string_agg in PostgreSQL

I have a simple Table Valued Query, where I include a string_agg() function. There is a full example at https://dbfiddle.uk/4WG7crbI (I know that the CTE is redundant, but it’s a simplification of a more complex function I’m working on). The tricky…
Manngo
  • 14,066
  • 10
  • 88
  • 110
0
votes
1 answer

SQL: SUB-QUERY in a JOIN statement returns 'Invalid Column Name' error

I have the following query where I have the following data: UPC LOCATION 0123 Albany 0123 Birmingham 0123 Austin 0124 Albany 0124 Birmingham 0125 Albany And I want the output to be: UPC LOCATION 0123 Albany, Birmingham,…
Will Buffington
  • 1,048
  • 11
  • 13
0
votes
2 answers

PostgreSQL - filter results with a LIKE on an aggregate field (string_agg)

I created an SQLFiddle scenario here but briefly: I need to aggregate the contents of a one to many relationship into a long string and then i need to filter those result by comparing the content of that aggregate field against another string ( or…
jmazzo
  • 37
  • 5
0
votes
1 answer

Use STRING_AGG to get Values from 3 different Tables

I am trying to get comma-separated values from the following 3 tables: Teams: Id Players: Id, Name TeamsPlayers: Id, TeamId, PlayerId What I am trying to get is a result like…
Sebastián Rubina
  • 89
  • 5
0
votes
1 answer

STRING_AGG gives wrong output for string values

I am executing a query that will fetch me the name of the doctor,the total number of patients that visited the doctor, the number of offices he went, based on the number of offices he went List out the name of the offices The query is as…
Luke
  • 51
  • 7
0
votes
0 answers

Why Does string_agg Not Make Changes In This Context

I was starting down the road of testing within group(), and I came across this unexpected behavior of string_agg (or maybe substring). In the following code, mType is a field containing things like 'HMA - etc.etc.etc.' or 'ACP - etc.etc.etc.' In…
Astennu
  • 19
  • 7
0
votes
2 answers

Django queryset StringAgg on arrayfield

I have some data which includes sizes, much like the model below. class Product(models.Model): width = models.CharField() height = models.CharField() length = models.CharField() Through annotation we have a field called at_size which…
S.D.
  • 2,486
  • 1
  • 16
  • 23
0
votes
0 answers

Migrating 'STUFF - For XML Path' into Azure Synapse String_Agg

Been looking at this from different dngles and read multiple post but still cant get this right. Can someone provide some guidance/help? Please, Thank you! Trying to run this in Synapse and getting errors due to the incompatible FOR XML PATH near…
Joorge_c
  • 1
  • 1
0
votes
1 answer

How can I get a SQL query to aggregate lists of results that correspond to the same name?

I was given the beginning of a SQL Query and am trying to adjust it to get the results we want. I have a large dataset that I want to have one line for each name and merchant id, and for the remaining 2 columns, I want the results to be in a list. …
hmd39
  • 3
  • 2
0
votes
1 answer

STRING_AGG() with duplicated values

I've found a lot of questions in here but none seems to resolve it. I do want to retrieve unique values with STRING_AGG() in SQL Server without using the keyword WITH. This is my query: SELECT DISTINCT bld.Code AS building_code, --…
alesssz
  • 384
  • 4
  • 18
0
votes
1 answer

unique constraint on window function output

I want to create lists of items and prevent the entry of 2 identical lists, using a unique constraint on a computed column. CREATE TABLE test_cc ( list_id int, list_item int, list_items AS STRING_AGG(CONVERT(varchar(10), list_item),',')…
Ludovic Aubert
  • 9,534
  • 4
  • 16
  • 28
0
votes
1 answer

In postgres, how to sort on, among others, a result value of a string_agg function, without selecting that field? Subquery not possible

I have this query (already stripped down but more complex in reality): SELECT e.id, string_agg(csb.description, ',' ORDER BY cs.id ASC) AS condition_set_descriptions FROM event e JOIN event_trigger_version etv ON e.event_trigger_version_id =…
Sebastiaan van den Broek
  • 5,818
  • 7
  • 40
  • 73
0
votes
1 answer

How do i use STRING_AGG and avoid duplicate values?

I face the following problem: in a SQL table i have stored Information about persons, date and diseases. CREATE TABLE Diseases (person varchar(80), time date, disease varchar(80)) INSERT INTO Diseases (person, time, disease) VALUES ('Harry',…
Philipp Schulz
  • 131
  • 1
  • 8
0
votes
2 answers

Group by with comma-separated values and excluding the value from the previous column value

I have two tables Lecturer and Student and I would like to display the other lecturer id's in the new column with comma separated values. I am using SQL Server 2014. Table: Lecturer id name subject ------------------------------- 102 …
learner123
  • 37
  • 1
  • 7
0
votes
0 answers

Can we make a line break on a "string_agg" Postgres?

I tried the method : STRING_AGG(cast(client.nom as varchar(1000)), ' ') In order to produce a line break when exporting my query results to CSV. This doesn't give me only one line break, but at least 5 for each record. How to make a…
user16898581
1 2
3
4 5