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

ASP.NET、C++/CLI、Java 何でもどうぞ

C# と VB.NET の入門サイト

SQLServerのSQLがわかりません。

[トピック内 14 記事 (1 - 14 表示)]  << 0 >>

■91620 / inTopicNo.1)  SQLServerのSQLがわかりません。
  
□投稿者/ 韋駄天 (5回)-(2019/07/16(Tue) 10:15:26)

分類:[.NET 全般] 

以下のようなテーブルがある場合に
A 1,2
B 3

カンマ区切りのデータを分割して以下のようにデータをとることはできますか?
A 1
A 2
B 3



引用返信 編集キー/
■91621 / inTopicNo.2)  Re[1]: SQLServerのSQLがわかりません。
□投稿者/ shu (1186回)-(2019/07/16(Tue) 11:05:39)
No91620 (韋駄天 さん) に返信
> 以下のようなテーブルがある場合に
> A 1,2
> B 3
>
> カンマ区切りのデータを分割して以下のようにデータをとることはできますか?
> A 1
> A 2
> B 3
>
単純なSQL文では厳しいかと思います。
ユーザー定義関数などを使用すると出来るかも。

使用するのがなんらかのアプリ上であれば取得してから編集するのはどうでしょう?
引用返信 編集キー/
■91622 / inTopicNo.3)  Re[2]: SQLServerのSQLがわかりません。
□投稿者/ Hongliang (841回)-(2019/07/16(Tue) 11:17:47)
SQL Server 2016なら、STRING_SPLITを使って
SELECT a, value AS b FROM table OUTER APPLY STRING_SPLIT(b, ',')
などと書けるようですが。
引用返信 編集キー/
■91623 / inTopicNo.4)  Re[1]: SQLServerのSQLがわかりません。
□投稿者/ 魔界の仮面弁士 (2236回)-(2019/07/16(Tue) 11:43:02)
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);

引用返信 編集キー/
■91625 / inTopicNo.5)  Re[1]: SQLServerのSQLがわかりません。
□投稿者/ 大谷刑部 (3回)-(2019/07/16(Tue) 11:51:05)
No91620 (韋駄天 さん) に返信
> 以下のようなテーブルがある場合に
> A 1,2
> B 3
>
> カンマ区切りのデータを分割して以下のようにデータをとることはできますか?
> A 1
> A 2
> B 3
>
>
原始的やり方ならselectを3つに分けて、union allにすればできるかと。
パターンが多くなる場合はお勧めできませんが。
引用返信 編集キー/
■91627 / inTopicNo.6)  Re[2]: SQLServerのSQLがわかりません。
□投稿者/ PANG2 (312回)-(2019/07/16(Tue) 12:59:49)
データをカンマ形式で持つのですか?
データ設計から見直したほうがよいでしょう。
SQL文が難しいのは適切な正規化が行われていないからでしょう。
引用返信 編集キー/
■91628 / inTopicNo.7)  Re[2]: SQLServerのSQLがわかりません。
□投稿者/ 韋駄天 (6回)-(2019/07/16(Tue) 13:51:47)
No91623 (魔界の仮面弁士 さん) に返信
> ■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);


実行してみました。
  A B
1 A 1
2 A 1
3 B 3

になりました。

引用返信 編集キー/
■91629 / inTopicNo.8)  Re[3]: SQLServerのSQLがわかりません。
□投稿者/ 韋駄天 (7回)-(2019/07/16(Tue) 14:03:10)
あくまで感覚ですが

TBL
NAME CODE
A 1,2
B 3

SELECT *
FROM TBL AS TBL1
LEFT OUTER JOIN (
----------
NAME = 'A' の時 1,2
NAME = 'B' の時 3
の列テーブルができればOKなのですが
----------
) AS TBL2
ON TBL2.NAME = TBL1.NAME

ちなみに2016ですが、STRING_SPLIT は使用できませんでした。
なので、以下のようなファンクションを作っています。

ALTER FUNCTION [dbo].[ufnStringSplit](
@Input AS NVARCHAR(MAX)
)
RETURNS @Output TABLE(Value NVARCHAR(MAX))
AS
BEGIN
IF @Input IS NOT NULL
BEGIN
DECLARE @Start INT = 1, @End INT = CHARINDEX(',', @Input), @Len INT = LEN(@Input);
WHILE @End > 0
BEGIN
INSERT INTO @Output (Value) VALUES(SUBSTRING(@Input, @Start, @End - @Start));
SELECT @Start = @End + 1, @End = CHARINDEX(',', @Input, @Start);
END
INSERT INTO @Output (Value) VALUES(SUBSTRING(@Input, @Start, @Len + 1 - @Start));
END
RETURN;
END
引用返信 編集キー/
■91633 / inTopicNo.9)  Re[3]: SQLServerのSQLがわかりません。
□投稿者/ 魔界の仮面弁士 (2240回)-(2019/07/16(Tue) 14:48:44)
No91628 (韋駄天 さん) に返信
>   A B
> 1 A 1
> 2 A 1
> 3 B 3
> になりました。

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


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);

引用返信 編集キー/
■91634 / inTopicNo.10)  Re[4]: SQLServerのSQLがわかりません。
□投稿者/ 韋駄天 (8回)-(2019/07/16(Tue) 14:54:13)
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 を使うとできることがわかってよかったです。



解決済み
引用返信 編集キー/
■91635 / inTopicNo.11)  Re[4]: SQLServerのSQLがわかりません。
□投稿者/ 魔界の仮面弁士 (2241回)-(2019/07/16(Tue) 15:08:40)
2019/07/16(Tue) 15:15:03 編集(投稿者)

No91628 (韋駄天 さん) に返信
> ちなみに2016ですが、STRING_SPLIT は使用できませんでした。
> なので、以下のようなファンクションを作っています。

STRING_SPLIT を使うには、互換性レベルが 130 以上であることが条件です。


No91629 (韋駄天 さん) に返信
> なので、以下のようなファンクションを作っています。

その関数を持っているなら:

SELECT TBL.A, U.Value
FROM TBL CROSS APPLY [dbo].[ufnStringSplit](TBL.B) U

で OK ですね。ただし互換性レベルが 90 以上であることが条件です。

-- SELECT name, compatibility_level FROM sys.databases


互換性レベルが 80 の場合は、先の XML 型で凌いでみてください。
(あるいは compatibility_level を変更してしまうか…)
引用返信 編集キー/
■91636 / inTopicNo.12)  Re[5]: SQLServerのSQLがわかりません。
□投稿者/ 韋駄天 (9回)-(2019/07/16(Tue) 16:09:21)
No91634 (韋駄天 さん) に返信
> ■No91631 (魔界の仮面弁士 さん) に返信
>>■No91628 (韋駄天 さん) に返信
>
> ありがとうございます。

互換性レベルは

SELECT compatibility_level
FROM sys.databases WHERE name = 'SV-TEST';

表示してみると 90 でした。
この互換性レベルはサーバーのレベルでしょうか?
それとも各PCごとに設定されるものでしょうか?
データベース名を指定する見たいなので、
データベースごとに設定されているのでしょうか?


データが以下のような場合
A 1,2 4
B 3  5,6

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



引用返信 編集キー/
■91637 / inTopicNo.13)  Re[6]: SQLServerのSQLがわかりません。
□投稿者/ 魔界の仮面弁士 (2242回)-(2019/07/16(Tue) 16:32:51)
No91636 (韋駄天 さん) に返信
> 表示してみると 90 でした。

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

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


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

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


> これで以下のように2列をそれぞれ分割することは可能ですか?
> 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 の場合、その行は抽出結果に現れなくなります。

引用返信 編集キー/
■91638 / inTopicNo.14)  Re[7]: SQLServerのSQLがわかりません。
□投稿者/ 韋駄天 (10回)-(2019/07/16(Tue) 17:03:42)
No91637 (魔界の仮面弁士 さん) に返信

ありがとうございました。
できました。
解決済み
引用返信 編集キー/

このトピックをツリーで一括表示


トピック内ページ移動 / << 0 >>

このトピックに書きこむ