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プールで実行すると以下のようなエラーが出ます。
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