1

I would like to write a query in serverless pool for concatenation of string values from multiple rows into single row with comma separated values. I am getting below error when I use COALESE function which I am unable to fix "Queries referencing variables are not supported in distributed processing mode"

Input rows : A B C A B Output row (Looking for distinct values only while creating a list like below) A,B,C

Pakard
  • 39
  • 1
  • 4

1 Answers1

0

You can use STRING_AGG() function to concatenate values from multiple rows to a single row with comma-separated.

Get distinct values of a column and apply STRING_AGG on the results as below.

select STRING_AGG(col1, ',') output_col1 from (select distinct col1 from #tb1) a

enter image description here

NiharikaMoola-MT
  • 4,700
  • 1
  • 3
  • 15
  • In the temp table, I'm trying to insert distinct values from the select query and it is getting the same error INSERT INTO #tb1 (col) SELECT DISTINCT test_col FROM mytable – Pakard Jan 21 '22 at 09:55
  • In short, I am looking for a query on mytable that returns multiple rows, and then insert those into #tb1 in a single loop. – Pakard Jan 21 '22 at 10:53