-3

Lets consider following table

CREATE TABLE TEST
(
    CityName NVARCHAR(20),
    Number INT
)

INSERT INTO TEST VALUES ('New Jork', 100)
INSERT INTO TEST VALUES ('London', 150)
INSERT INTO TEST VALUES ('Paris', 110)

Based on first column I want to get comma-separated values in a string, expected result - "New Jork, London, Paris"

I tried:

SELECT STRING_AGG(CityName, ',') FROM TEST

But instead of comma-separated values in one string; I got output table of size 1x1 with desired values.

I also tried resolution from following question: SQL Server convert select a column and convert it to a string

But it doesn't work on Azure returning error as follows:

A variable that has been assigned in a SELECT statement cannot be included in a expression or assignment when used in conjunction with a from clause.

Thom A
  • 88,727
  • 11
  • 45
  • 75
szaki
  • 3
  • 1
  • 1
    `SELECT STRING_AGG(CityName, ',') FROM TEST` Seems to be *exactly* what you want. I don't understand why having a table containing a scalar string type value *isn't* what you're after. Perhaps you can elaborate? – Thom A Sep 01 '23 at 07:58
  • Let's assume, later I want to do something like that: ```declare @var='New Jork, London, Paris'``` and ```select @var from TEST2``` – szaki Sep 01 '23 at 08:24
  • So you want to assign that value to a variable? Then it's just `SELECT @VariableName = STRING_AGG`... Just like any other assignement. – Thom A Sep 01 '23 at 08:26
  • Also your title says Azure Synapse but your tags say Azure SQL DAtabase; which are you *really* using as they are different products (and have quite different support for T-SQL). – Thom A Sep 01 '23 at 08:27
  • the script ```SELECT @VariableName = STRING_AGG``` just allows me to assign 1x1 table to my variable but I still need assign string to my variable, not table – szaki Sep 01 '23 at 08:39
  • *" still need assign string to my variable"* That's what `SELECT @VariableName = STRING_AGG...` does... – Thom A Sep 01 '23 at 08:40
  • @szaki all queries return result sets, not individual values. ALL, in all database products. If that result contains a single value, you can assign it to a variable without problems. That's how *all* SQL Server code works, for 30 years. Even the question you link to uses `STRING_AGG`. The accepted answer is too old but even that would never produce the error you posted – Panagiotis Kanavos Sep 01 '23 at 08:48
  • 1
    @szaki I suspect there's a misconception of how SQL works. Perhaps you have code in a client language (C#? Python?) that tried to use the results the wrong way? Post code that demonstrates the actual problem and the actual error you get. Something that other people can execute and reproduce the problem and come up with a solution – Panagiotis Kanavos Sep 01 '23 at 08:50
  • I haven't used Synapse, @PanagiotisKanavos, but the self-referencing variable antipattern might produce the error on it; the implementation of T-SQL is limited / *different* on Synapse. – Thom A Sep 01 '23 at 08:55
  • I've closed this as a duplicate of the linked post as this [answer](https://stackoverflow.com/a/53858417/2029983) is what you need/want. – Thom A Sep 01 '23 at 09:06
  • I'm not sure this is a duplicate, or that it can be answered easily: the OP seems to misunderstand how SQL, the language, works. There are a lot of duplicates about string concatenation. `STRING_AGG` works better than any other - [4 times faster than FOR XML](https://sqlperformance.com/2016/12/sql-performance/sql-server-v-next-string_agg-performance) which makes it [80 times faster](https://sqlblog.org/2011/03/08/t-sql-tuesday-16-this-is-not-the-aggregate-youre-looking-for) than the unsupported concatenation – Panagiotis Kanavos Sep 01 '23 at 09:23

0 Answers0