SELECT句の中に変数を割り当てることが出来ない

SynapseAnalytics SQLプールでTSQLの記載をしていると、SQL Serverでは利用できるのですが、Synapse Analytics SQLプールでは利用できないものがあります。

SELECT句で変数を割り当てるときに、以下のTSQLはSQL Serverでは問題なく動きます。

--LINITEMテーブルに含まれている列名を1行でカンマ区切りで出力する
DECLARE @Columns nvarchar(max)
SET @Columns = NULL
SELECT @Columns = concat(@Columns,c.name,',')
FROM sys.columns c 
WHERE c.object_id=object_id('LINEITEM')
PRINT @Columns

ただ、このTSQLをSynapseAnalytics SQLプールで実行すると以下のようなエラーが出ます。

メッセージ 104473、レベル 16、状態 1、行 1

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.

これは困ったとなりまして、色々調べますとSTRING_AGG()関数で回避できました。
以下のTSQLで上手く実行することが出来ます。

DECLARE @Columns nvarchar(max)
SET @Columns = NULL
SELECT @Columns = STRING_AGG(c.name,',')
FROM sys.columns c 
WHERE c.object_id=object_id('LINEITEM')
PRINT @Columns 

--結果
L_ORDERKEY,L_PARTKEY,L_SUPPKEY,L_LINENUMBER,L_QUANTITY,L_EXTENDEDPRICE,L_DISCOUNT,L_TAX,L_RETURNFLAG,L_LINESTATUS,L_SHIPDATE,L_COMMITDATE,L_RECEIPTDATE,L_SHIPINSTRUCT,L_SHIPMODE,L_COMMENT

STRING_AGGの用途

文字列式の値を連結し、値の間に区切り記号を挿入します。
また、文字列の末尾に区切り記号は追加されません。
文字列以外を連結した場合は、NVARCHAR型に変換されます。

STRING_AGGの使い方

STRING_AGG (カラム名, 区切り文字) WITHIN GROUP (ORDER BY ソート条件)

使用例

以下のようなテーブルを作成します。

id	col1	col2
1	AAAA	1
2	BBBB	0
3	CCCC	1
4	DDDD	1
5	EEEE	0

create table test_string_agg
(
	id int,
	col1 nvarchar(30),
	col2 nvarchar(30)
);
insert into test_string_agg values (1,'AAAA','1');
insert into test_string_agg values (2,'BBBB','0');
insert into test_string_agg values (3,'CCCC','1');
insert into test_string_agg values (4,'DDDD','1');
insert into test_string_agg values (5,'EEEE','0');

col2でデータをグルーピングして、idとcol1のそれぞれの値をカンマ区切りで連結した値を取得します。

select
	 STRING_AGG(id,',') WITHIN GROUP(ORDER BY id) as ids
	,STRING_AGG(col1,',') WITHIN GROUP(ORDER BY id) as col1s
	,col2
from
	test_string_agg
group by col2;

--結果
ids　　　col1s          col2
1,3,4   AAAA,CCCC,DDDD  1
2,5     BBBB,EEEE       0