他のテーブルに定義した置換パターンによる置換、表示フラグによる表示ONOFF

Question

お世話になっております。
いま、テーブル1とテーブル2があります。

テーブル1.就業日報

社員番号	実事由	備考
1	有休	通院のため
2	午前休,直行	取引先A
3	欠勤	私用
4	出張	取引先B

テーブル2.実事由一覧

実事由	表示名	備考非表示
有休	休暇	true
欠勤	休暇	true
午前休	AM休	true
直行	出張	false
出張	出張	false

ここで、次のビューを作りたいです。（ssmsで作成したいです。）

• テーブル1を元にしたもの
• テーブル1の「実事由」を「実事由一覧」テーブルの実事由フィールドから表示名フィールドに置換
• テーブル1の「実事由」のうち「実事由一覧」にて備考非表示をtrueに設定している要素が一つでもある場合、備考を空欄にする

具体的には、次のような形です。

希望のビュー

社員番号	事由	備考
1	休暇
2	AM休,出張
3	休暇
4	出張	取引先B

ここで次のようなことはできますでしょうか。
0. テーブル2の実事由と表示名を基にテーブル1の実事由を置換すること
（テーブル1の実事由に1フィールド1事由しかなければ、left joinでできるのですが、社員番号2のようなケースが避けられないです）

1. テーブル2の実事由と備考非表示を基にテーブル1の備考を表示/非表示すること

（上同様に、1フィールド1事由であればleft joinしてiifでできるのですが…）

Comments

[sazi]

> テーブル2の実事由と備考非表示を基にテーブル1の備考を表示/非表示すること
事由が複数の時、非表示が一つでもあれば非表示という事ですか？

また、
> ビューを作りたいです。
create viewしたいという事ですか？
> ssmsで作成したいです。
答えになっていませんが、問い合わせのSQLという事だと解釈しました。

[tokita.]

>> テーブル2の実事由と備考非表示を基にテーブル1の備考を表示/非表示すること
>事由が複数の時、非表示が一つでもあれば非表示という事ですか？
はい、そうです。

また、
>> ビューを作りたいです。
>create viewしたいという事ですか？
SSMSでそういうビューを作って見れる状態にしたいという意図です。

Answer 1

1フィールド1事由であればleft joinしてiifでできるのですが

STRING_SPLITを利用すると、表に展開できます。
これをAPPLY 演算子で繋ぐと実事由を分解した形が展開できますので、加工すれば良いかと思います。

select 就業日報.*, value as 単実事由
from 就業日報 OUTER APPLY STRING_SPLIT(実事由, ',')

Comments

[tokita.]

回答ありがとうございます。とりあえずできました

ビュー：習作1
```SQL
SELECT 就業日報.*, value as 単実事由,表示名, 備考非表示
FROM 就業日報 OUTER apply STRING_SPLIT(実事由, ',') s LEFT JOIN
実事由一覧 AS c ON c.実事由 = s.value
```
ビュー：習作2
```SQL
SELECT 社員番号, string_agg(表示名, ',') AS 事由, iif((CHARINDEX('1', string_agg(備考非表示, ',')))>0,'',備考) AS 備考
FROM dbo.習作1
GROUP BY 社員番号,備考
```

これの実現のために習作1という作業途中のビューを作らないとうまく動きませんでした。
習作1のSQLをかっこで囲んだものを習作2のFROMのサブクエリとして入れれば動くかと思っていたのですが。。
こちら1つのビューで実現する方法はございますでしょうか。

[sazi]

以下ではどうでしょうか
SELECT 社員番号, string_agg(c.表示名, ',') AS 事由, iif((CHARINDEX('1', string_agg(c.備考非表示, ',')))>0,'', s.備考) AS 備考
FROM (
SELECT 就業日報.*, value
FROM 就業日報 OUTER apply STRING_SPLIT(実事由, ',')
) s LEFT JOIN 実事由一覧 AS c
ON c.実事由 = s.value
GROUP BY 社員番号, 備考

[sazi]

若しくは
with 習作1 as (
SELECT 就業日報.*, value as 単実事由,表示名, 備考非表示
FROM 就業日報 OUTER apply STRING_SPLIT(実事由, ',') s LEFT JOIN
実事由一覧 AS c ON c.実事由 = s.value
)
SELECT 社員番号, string_agg(表示名, ',') AS 事由, iif((CHARINDEX('1', string_agg(備考非表示, ',')))>0,'',備考) AS 備考
FROM 習作1
GROUP BY 社員番号,備考