テーブルのレコード内でNULLではないレコードの列名のみ取得する

問題の概要

Azure Synapase Analytics 専用SQLプール（SQL Serverと互換性の高いAzureのDWH向けPaaSサービス）で運用を行っておりまして、以下のようなテーブル構造を持っていました。

id	col1	col2	col3	col4	col5
1	AA	NULL	D	G	NULL
2	BB	A	E	NULL	I
3	CC	NULL	F	NULL	J
4	DD	B	NULL	H	NULL
5	EE	C	NULL	NULL	K

実際には200以上のカラムがあったのですが、記事の関係上id列~col5列までの6列で記載します。この時に、id=1のレコード内に、NULLが格納されていない列の列名をSQLで取得します。
id=1の場合：col1,col3,col4

また、その他のidの値でも同様に各レコードの中でNULLが格納されていない列の列名をSQLで取得し出力させます。イメージは以下の通りです。

条件	取得したい結果
id=1	col1,col3,col4
id=2	col1,col2,col3,col5
id=3	col1,col3,col5
id=4	col1,col2,col4
id=5	col1,col2,col5

と言う事がやりたかったのですが、実現するのに時間がかかったので、同じように困っている人もいる（？）かと思い、解決方法をこちらに記載させていただきます。

解決方法

ざっと検証するテーブルとデータの準備をします。

検証用テーブルとデータの準備

create table test
(
	 id int
	 ,col1 nvarchar(10)
	 ,col2 nvarchar(10)
	 ,col3 nvarchar(10)
	 ,col4 nvarchar(10)
	 ,col5 nvarchar(10)
)
GO

insert into test(id,col1,col2,col3,col4,col5) values(1	,'AA'	,NULL	,'D'	,'G'	,NULL)
insert into test(id,col1,col2,col3,col4,col5) values(2	,'BB'	,'A'	,'E'	,NULL	,'I')
insert into test(id,col1,col2,col3,col4,col5) values(3	,'CC'	,NULL	,'F'	,NULL	,'J')
insert into test(id,col1,col2,col3,col4,col5) values(4	,'DD'	,'B'	,NULL	,'H'	,NULL)
insert into test(id,col1,col2,col3,col4,col5) values(5	,'EE'	,'C'	,NULL	,NULL	,'K')
GO

UNPIVOTはNULL値を返さない

UNPIVOTは入力に含まれるNULL値が出力されません。これをうまく使うといい感じの出力ができました。

UNPIVOTを使ってNULLではない列名を取得

select
	 id
	,STRING_AGG(col,',') AS col_names
from
	test
unpivot
	(val for col in (col1,col2,col3,col4,col5)) as up
group by
	id
order by id
GO

結果

動的にUNPIVOTを生成

今回の検証用テーブルは6列しかありませんが、実際は200列以上のテーブルだったため、SQLでUNPIVOTを手で書くのが大変だったので、動的にUNPIVOTを生成するように上記SQLを改造しました。

UNPIVOTを使ってNULLではない列名を取得（動的にUNPIVOTを生成）

DECLARE @column_list varchar(max)
DECLARE @sql nvarchar(max)
DECLARE @table_name nvarchar(max)

--取得する対象のテーブル
SET @table_name = 'test'

--動的にカラムを取得する
SET @column_list = null
select
    @column_list = STRING_AGG(c.name,',')
from
	sys.tables t inner join sys.columns c on 
	t.object_id = c.object_id
where
	t.name = @table_name
	and c.name != 'id'

--SQL（UNPIVOT）を生成
SET @sql =
	'select '									+
	'	 id	'									+
	'	,STRING_AGG(col,'','') AS col_names '	+
	'from '										+
		@table_name + ' '						+
	'unpivot '									+
	'	(val for col in (' + @column_list +')) as up ' +
	'group by id '								+
	'order by id'

--生成したSQLを実行
EXEC sp_executesql @sql
GO

結果

UNPIVOT/PIVOTは使いこなせばなかなか便利かもしれません！！！