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

vote

2 answers

SQL BQ Return user's best and worst rated movie

I have data in format: user_id user_name movie_rating movie_name 1 x 1 asd1 1 x 3 asd2 1 x 5 asd3 1 x 5 asd4 2 y 2 asd4 2 y 3 asd5 2 y 4 asd6 I want to extract the best and the worst rated movie per user in a format where…

sql google-bigquery string-agg

asked Apr 21 '21 at 07:47

Yolohukan

vote

3 answers

How to get strings comma separated in ascending order with STRING_AGG()

My table looks like this Color Order ------------ Red 49 Blue 32 Green 80 Green 30 Blue 93 Blue 77 Red 20 Green 54 Red 59 Red 42 Red 81 Green 35 Blue 91 My Query is SELECT Color, Count(*) AS Count, STRING_AGG(Order,',')…

sql sql-server tsql string-aggregation string-agg

asked Mar 30 '21 at 02:58

asmgx

7,328
15
82
143

vote

2 answers

SQL Server - indexed view with string_agg

I try to define an indexed view to create full text search index on it. The view itself is created correctly: CREATE OR ALTER VIEW dbo.my_view WITH SCHEMABINDING AS SELECT p.id as protector_id, p.name as protector_name, …

sql-server indexed-view string-agg

asked Jan 20 '21 at 12:23

r34

vote

1 answer

Eliminate null results from case query - no null values in source

I am trying to pull off a complicated aggregation and concatenation query to prep data for import to a website, aggregating multiple results rows and columns into one row per ID. I'm almost there, except that I am getting a lot of NULL results…

sql-server pivot case aggregate-functions string-agg

asked Dec 28 '20 at 20:59

smallnorthumbrianvillage

vote

1 answer

Reverse to STRING_AGG

Here is a sample data on which we are further processing. create table #tmp (id int identity(1,1), na varchar(10),me varchar(10)) insert into #tmp (na,me) values ('a','t'), ('a','u'), ('a','v'), ('a','w'), ('b','x'), ('b','y'), ('b','z') select *…

sql sql-server split cursor string-agg

asked Sep 22 '20 at 06:42

Sadiqabbas Hirani

vote

1 answer

PostgreSQL and STRING_AGG

I have two tables. CREATE TABLE document ( doc_id varchar PRIMARY KEY, title varchar, content varchar ); CREATE TABLE doc_spec_sets ( tc_set_id varchar PRIMARY KEY, doc_id varchar, spec varchar ); And data: insert into document values('doc1',…

postgresql string-agg

asked Sep 07 '20 at 09:33

Sergey Kozlov

vote

1 answer

STRING_AGG working on compatibility level < 140

I understand STRING_AGG is introduced in SQL Server 2017 which is compatibility level 140. The expected behaviour is to get error message 'string_agg' is not a recognized built-in function when run on 2016 or lower versions. Got error when I run on…

sql-server azure-sql-database compatibility-level string-agg

asked Mar 31 '20 at 08:02

dejjub-AIS

1,501
2
24
50

vote

1 answer

How to use String_agg in bigquery

I have a list of codes for same IDs with ranking, I want to have these codes in a coma separated way by there rank in ASC order like (71,49,490,41,61,42,62,614,615,621), when I am using string_agg(code,"," ORDER BY rank_) it is showing below…

google-bigquery string-agg

asked Feb 03 '20 at 13:59

Fahim Zaman Anik

votes

2 answers

How to concatenate N rows of a column based on a specific column value based on a few conditions in Google BigQuery?

TimeStamp USER ID string_col 1 100001 Here 2 100001 there 5 100001 Apple 1 200002 this is 2 200002 that is 3 200002 Apple 4 200002 some 5 200002 summer 6 200002 winter 8 200002 Apple 9 200002 Apple That is my raw…

sql google-bigquery window-functions string-agg

asked Aug 03 '23 at 02:00

user22329205

votes

2 answers

How to concatenate N rows of a column based on a specific column value using a condition in Google BigQuery?

TimeStamp USER ID string_col 1 100001 Here 2 100001 there 3 100001 Apple 4 200002 this is 5 200002 that is 6 200002 Apple 7 200002 some 8 200002 summer 9 200002 winter 10 200002 Apple That is my raw table & I want to…

sql google-bigquery window-functions string-agg

asked Aug 02 '23 at 23:29

user22329205

votes

1 answer

Appending data to existing string_agg in bigquery

when a person pings or calls, we should be able to show that data and if he doesn't do, it should show as null but when he makes multiple pings or calls, it is appending as multiple array. below is the sample data in that I'm getting…

google-cloud-platform google-bigquery array-agg string-agg

asked Jun 14 '23 at 20:34

jay

votes

2 answers

Massive delete based on subquery on WHERE statement. Postgres

I need made a subquery with IN statement. Like this (simplify): DELETE FROM table_1 WHERE id_table_1 IN (SELECT string_agg(fk_id_table_1::TEXT,',') FROM table_2 I need made a massive delete, based on second subquery. The problem is if i use IN…

sql postgresql subquery any string-agg

asked Jun 07 '23 at 00:22

Alvaro

votes

1 answer

STRING_AGG() Error. Conversion failed when converting the varchar value to data type int

I needed to convert a table insert/update trigger to a Service Broker task. The INSERTED table is used but is passed from the Trigger as XML variable to the SP and executed asynchronously under the Broker Service. Again this is not the issue as I…

sql sql-server tsql type-conversion string-agg

asked May 25 '23 at 06:32

Andye

votes

1 answer

SAP HANA SQL store STRING_AGG to variable in procedure

I have a procedure where I need to get the fields of a column (in SAP HANA database). I need to assign the output of STRING_AGG function to a variable CREATE procedure TAB_COMP (IN T1 VARCHAR(30), IN T2 VARCHAR(30)) LANGUAGE SQLSCRIPT SQL SECURITY…

sql stored-procedures hana string-agg

asked May 22 '23 at 16:40

TrenT

votes

1 answer

SQL - Pivot Column To Rows with Group By and Concat

I need to traspose a column into a row ( by concatenating it) with group by... using : sql server…

sql sql-server tsql string-aggregation string-agg

asked May 12 '23 at 15:26

Enrico

Prev 1

3 4 5 Next