C# と VB.NET の質問掲示板

■No91620 (韋駄天 さん) に返信
> カンマ区切りのデータを分割して以下のようにデータをとることはできますか？

-- SQL Server 2008 R2

CREATE TABLE TBL ( A nchar(1) PRIMARY KEY, B nvarchar(10) );

INSERT INTO TBL (A, B) VALUES (N'A', N'1,2'), (N'B', N'3');

WITH C AS (
  SELECT A, D = CAST(N'<e>'+REPLACE(B,N',',N'</e><e>')+ N'</e>' AS XML)
  FROM TBL
) SELECT A, D.value(N'.', 'NCHAR(1)') AS B
FROM C CROSS APPLY D.nodes(N'e') AS F(G);

■No91628 (韋駄天 さん) に返信
> 　　Ａ　Ｂ
> １　Ａ　１
> ２　Ａ　１
> ３　Ｂ　３
> になりました。

失礼しました。こうですね。


WITH C AS (
  SELECT A, D = CAST(N'<e>'+REPLACE(B,N',',N'</e><e>')+N'</e>' AS XML)
  FROM TBL
)
SELECT
  C.A
--, C.D.value(N'.', 'NCHAR(2)') AS B
  , F.G.value('.', 'NCHAR(2)') AS B
--, C.D AS B
FROM C CROSS APPLY D.nodes(N'e') AS F(G);

■No91631 (魔界の仮面弁士 さん) に返信
> ■No91628 (韋駄天 さん) に返信

ありがとうございました。
CROSS APPLY で
検索したら以下のが見つかりました。
少し違うのですが

StudentID	FirstName	LastName	Birthday		Gender	TestIDList
1		Taro		Yamada		1980-02-15 00:00:00.000	M	1,2
2		Hanako		Tanaka		1979-12-30 00:00:00.000	F	1
3		Yuko		Suzuki		1979-07-07 00:00:00.000	F	NULL
4		Takao		Sato		1980-03-12 00:00:00.000	M	3
5		Hiroki		Takagi		1979-04-05 00:00:00.000	M	1,2,3
6		Yuka		Kimura		1981-03-27 00:00:00.000	F	2

このデータテーブルで

SELECT  * 
FROM    T_Student AS S
CROSS APPLY dbo.ufnStringSplit(TestIDList); 

を実行すると

StudentID	FirstName	LastName	Birthday		Gender	TestIDList	Value
1		Taro		Yamada		1980-02-15 00:00:00.000	M	1,2		1
1		Taro		Yamada		1980-02-15 00:00:00.000	M	1,2		2
2		Hanako		Tanaka		1979-12-30 00:00:00.000	F	1		1
4		Takao		Sato		1980-03-12 00:00:00.000	M	3		3
5		Hiroki		Takagi		1979-04-05 00:00:00.000	M	1,2,3		1
5		Hiroki		Takagi		1979-04-05 00:00:00.000	M	1,2,3		2
5		Hiroki		Takagi		1979-04-05 00:00:00.000	M	1,2,3		3
6		Yuka		Kimura		1981-03-27 00:00:00.000	F	2		2

なので、Value を TestIDList と置き換えれば出来上がりですね。

WITHの中身が少し理解できませんが、たぶんXMLを使用してカンマで分割しているのだと思います。
XMLを使用する方法も見たのですが、使ったことがないので理解できませんでした。
もう少し勉強します。

CrossApply を使うとできることがわかってよかったです。

■No91636 (韋駄天 さん) に返信
> 表示してみると 90 でした。

下記の構文で変更することもできます。
DB 管理者の方と相談する必要はあるでしょうけれども。

ALTER DATABASE [SV-TEST]
SET COMPATIBILITY_LEVEL = 120;


> この互換性レベルはサーバーのレベルでしょうか？
> データベースごとに設定されているのでしょうか？

サーバー単位でもクライアント単位でもなく、データベース単位です。
たとえば同じサーバー内に、互換性レベル 100 と 90 と 80 のデータベースが同居することもあります。


> これで以下のように２列をそれぞれ分割することは可能ですか？
> A　1　　4
> A　2　　4
> B　3　　5
> B　3　　6

互換性レベルが 90 なのであれば、
先の自作関数を通じて、こんな感じですね。


/*
CREATE TABLE TBL ( COL1 nchar(1) PRIMARY KEY, COL2 nvarchar(10), COL3 nvarchar(10) );

INSERT INTO TBL (COL1, COL2, COL3) VALUES (N'A', N'1,2', N'4'), (N'B', N'3', N'5,6');
*/
SELECT
   TBL.COL1 AS Field1 
,  X.Value AS Field2
,  Y.Value AS Field3
FROM TBL
   CROSS APPLY [dbo].[ufnStringSplit](COL2) X
   CROSS APPLY [dbo].[ufnStringSplit](COL3) Y


注意点：
・COL2 の値が 5 つ、COL3 の値が 3 つの場合、CROSS 結果は 15 レコード分に展開されます。
・COL2 および COL3 が空文字列の場合は、単一値が設定されていた場合と同様に展開されます。
・COL2 や COL3 が NULL の場合、その行は抽出結果に現れなくなります。