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
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
10
votes
0 answers

string_agg is not recognized built-in function name - SQL Server 12.0

I want to group by a table by some columns and show values of other column.in SQL v.2017 I can do that with string_agg function. but my SQL version is 2012.How can I do that... create table dbo.TaskPeriods ( [id] [int] NULL, [startDate] [datetime]…
mohammadrg
  • 151
  • 1
  • 8
5
votes
1 answer

STRING_AGG with CASE WHEN

The schema CREATE TABLE person ( [first_name] VARCHAR(10), [surname] VARCHAR(10), [dob] DATE, [person_id] INT ); INSERT INTO person ([first_name], [surname], [dob] ,[person_id]) VALUES ('Alice', 'AA', '1/1/1960', 1), …
Michal Rosa
  • 2,459
  • 2
  • 15
  • 28
3
votes
1 answer

SQL Server 2017 STRING_AGG Order By

I'm using SQL Server 2017 Microsoft SQL Server 2017 (RTM-GDR) (KB4583456) - 14.0.2037.2 (X64) Nov 2 2020 19:19:59 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 (Build 19042: ). I'm trying to sort…
evanburen
  • 267
  • 4
  • 16
3
votes
1 answer

SQL Server STRING_AGG function sorting is not working as expected

I've broken my head against this issue trying to reproduce it on a dynamically generated data set, but it finally worked out! This is the code ;with tbl as ( select Id, ClCode, Manager, ChangeDate from (values (1, '000005', 'Cierra…
Andrey Morozov
  • 7,839
  • 5
  • 53
  • 75
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
1
vote
2 answers

Concat SQL row into comma separated list

I'm having an issue trying to Concat a comma separator list within SQL when the data matches. I have a table with 6 columns. I want to check if the LicenceNumbers match and then conconcatenate the ItemsNumbers into a comma separator list if the…
Michael Thomas
  • 29
  • 4
1
vote
1 answer

SQL Server Management Studio: Policy - ExecuteSql throws error if the statement string contains an empty field

I want a SQL Server Management Policy to check the values in a delimited list returned by FOR XML. The problem is that "FOR XML ('')" will throw a SQL error. I may not post screenshots or site-specific details so I will demonstrate the error using a…
Bruce Dimon
  • 161
  • 10
1
vote
2 answers

string_agg () is not working for Postgres sql when joining on multiple tables

I have to join user_name from user table where as first_name and last_names from user_profile table. Until here everything is fine, but when I try to fetch respective roles assigned from user_role tables it gives multiple rows for single user as 1…
kah
  • 71
  • 9
1
vote
2 answers

I need help identifying syntax error with T-SQL string agg function using convert varchar(max)

I'm attempting the string_agg function with convert(varchar(max)) because the results exceededs the 8000 characters limit, but now I am getting a syntax error under WITHIN GROUP (ORDER BY PURP.PROGRAM_YEAR DESC) AS 'GS SUMMARY' Here is what I…
Kendra
  • 11
  • 3
1
vote
3 answers

How to compare multiple rows

I have a table with data like following, want to return those group_id with unique data. Both group_id 3 and 4 have two component 123 and 456, so they are "duplicated", we just need to return the smaller group_id, that's 3. Also group_id 5 doesn't…
zhao xiongwei
  • 15
  • 3
1
vote
1 answer

Put strings inside quotes with string_to_array()

I am using the following query: WITH a as (SELECT unnest(string_to_array(animals, ',')) as "pets" FROM all_animals where id = 100) select * from a which returns the following data: 1 Cat 2 Dog 3 Bird My question is, how can I format my…
ArthurJ
  • 777
  • 1
  • 14
  • 39
1
vote
6 answers

Put numbers for each rows in STRING_AGG in SQL Server

I have to put some columns and group them together by STRING_AGG also I want to put number first of each rows What I have: Name Cake Coca ice-cream Same one five six Sara one one NULL John two two NULL I want the output be something…
Sara Moradi
  • 35
  • 6
1
vote
0 answers

Use distinct in string_agg function in SQL Server

Let's assume that I have this table: area room Area_1 Room1 Area_1 Room2 Area_1 Room3 Area_2 Room4 Area_2 Room5 I would like to get this result areas | rooms -----------------+--------------------------------------- Area_1 |…
d2907
  • 798
  • 3
  • 15
  • 45
1
vote
1 answer

Is it possible to concatenate using STRING_AGG on an array that was created with SPLIT() IN BigQuery

I am trying to use string_agg to concatenate an array that was created using SPLIT(). The code for the array I'm trying to concatenate looks like this: ARRAY ( SELECT AS STRUCT SPLIT(CustomField.SubComponents, ' | ') AS Name ) AS…
Bianca Rodriguez
  • 65
  • 5
1
2 3 4 5