複数カンマの位置を保存するためには

質問

いろいろと試したのですが、うまくいかず質問させて頂ければと思います。

現在、以下のようなテーブルとデータがあります。
テーブル名: sample_tbl
ID string_char
1 a<d<b<aa<dd<<b<87<hg<
2 1f3f<3rtf<33e<rf<dd<rf<rf<ed<rf<rf
3 f<44fg<rf<
これをCHARINDEXなどで分割し、以下のようなテーブルを作りたいのです。'<'の場所の位置、最大9個まで保存したいのです。IDはすべてユニークです。

ID string_char　 　　　　　　　　　　　　　　 　　　pos_1 pos_2 pos_3(pos_9まで）
1 a<d<b<aa<dd<<b<87<hg< 　　　　　　　　　　　 2 　 4 　 6
2 1f3f<3f<e<rf<d<rf<<d<rf<rf 　　　　　　　　　　 5 　 8 　 10
3 f<44fg<rf 　　　　　　　　　　　　　　 　　　　　　　　2 　 7 　 0
私が取った方法は単純なもので、最初にCHARINDEXでpos_1を出したあとにその位置から'<'の場所を数えて足していく非効率で、ディスクの容量を取ってしまい、時間がかかる方法でした。Pos_9までいくのは大変でした。もし効率的な方法があればどうかよろしくご教授ください。

回答No.3

jamshid6 · ベストアンサー

>メッセージ 9411、レベル 16、状態 1、行 2　XML の解析: 行 1、文字 69。セミコロンが必要です
なるほど。うっかりしていました。文字列の中には「&」も含まれていたのですね。
XMLでは特殊な扱いなので、エスケープが必要ですね。
手直し後のクエリは以下の通りです（別にMAXSEQ>=...となっているところはMAXSEQ>..が正しいのでそこも直しました）。
うまく動いたら、何やっているか調べてみてもいいかもしれません。

SELECT
a.string_char,
b.*
FROM (
SELECT
string_char,
CONVERT(xml,'<T>'+REPLACE(REPLACE(string_char,'&','*'),'<','</T><T>')+'</T>') string_xml
FROM dbo.sample_tbl) a
CROSS APPLY
(SELECT
CASE WHEN MAXSEQ>1 THEN SUM(CASE WHEN SEQ<=1 THEN COLLEN ELSE 0 END)
+MAX(CASE WHEN SEQ<=1 THEN SEQ ELSE 0 END) ELSE 0 END pos_1,
CASE WHEN MAXSEQ>2 THEN SUM(CASE WHEN SEQ<=2 THEN COLLEN ELSE 0 END)
+MAX(CASE WHEN SEQ<=2 THEN SEQ ELSE 0 END) ELSE 0 END pos_2,
CASE WHEN MAXSEQ>3 THEN SUM(CASE WHEN SEQ<=3 THEN COLLEN ELSE 0 END)
+MAX(CASE WHEN SEQ<=3 THEN SEQ ELSE 0 END) ELSE 0 END pos_3,
CASE WHEN MAXSEQ>4 THEN SUM(CASE WHEN SEQ<=4 THEN COLLEN ELSE 0 END)
+MAX(CASE WHEN SEQ<=4 THEN SEQ ELSE 0 END) ELSE 0 END pos_4,
CASE WHEN MAXSEQ>5 THEN SUM(CASE WHEN SEQ<=5 THEN COLLEN ELSE 0 END)
+MAX(CASE WHEN SEQ<=5 THEN SEQ ELSE 0 END) ELSE 0 END pos_5,
CASE WHEN MAXSEQ>6 THEN SUM(CASE WHEN SEQ<=6 THEN COLLEN ELSE 0 END)
+MAX(CASE WHEN SEQ<=6 THEN SEQ ELSE 0 END) ELSE 0 END pos_6,
CASE WHEN MAXSEQ>7 THEN SUM(CASE WHEN SEQ<=7 THEN COLLEN ELSE 0 END)
+MAX(CASE WHEN SEQ<=7 THEN SEQ ELSE 0 END) ELSE 0 END pos_7,
CASE WHEN MAXSEQ>8 THEN SUM(CASE WHEN SEQ<=8 THEN COLLEN ELSE 0 END)
+MAX(CASE WHEN SEQ<=8 THEN SEQ ELSE 0 END) ELSE 0 END pos_8,
CASE WHEN MAXSEQ>9 THEN SUM(CASE WHEN SEQ<=9 THEN COLLEN ELSE 0 END)
+MAX(CASE WHEN SEQ<=9 THEN SEQ ELSE 0 END) ELSE 0 END pos_9
FROM
(SELECT MAX(SEQ) OVER () MAXSEQ, SEQ, COLLEN
FROM
(SELECT
ROW_NUMBER() OVER (ORDER BY COL) SEQ,
ISNULL(LEN(x.COL.value('./text()[1]','varchar(100)')),0) COLLEN
FROM a.string_xml.nodes('/T') as x(COL)
) b1) b2
GROUP BY MAXSEQ) b

お礼

ありがとうございます！PIVOTを使ってシーケンスを集計して
やってはみましたが、だめだったので今回投稿させて頂きました。
OracleにはLAGがあるのですが、SQLServerだと該当するのがなくて
困り果てていたわけなのです。

どうもありがとうございます！

回答No.2

jamshid6

#1です。
失礼しました。「@S」となっているところを「sample_tbl」にしてください。

＃このような複雑なクエリをソラで書けるほど頭脳明晰ではないので、実際に書いてみたわけですが、そのときに「@S」というテーブル変数を使って書いておりました。貼る前に直しておくべきでしたね。

補足

jamshid6様、いつも参考にさせて頂いております。XNL吐き出しとは
初心者の私には雲の上の世界です。

以下を実行いたしますと「メッセージ 9411、レベル 16、状態 1、行 2　XML の解析: 行 1、文字 69。セミコロンが必要です。」と出てしまいます。一応、ラストにセミコロンを入れてみたのですが、うまく動きません。どうしたらよいでしょうか？

SELECT
a.string_char,
b.*
FROM (
SELECT
string_char,
CONVERT(xml,'<T>'+REPLACE(string_char,'<','</T><T>')+'</T>') string_xml
FROM dbo.sample_tbl) a
CROSS APPLY
(SELECT
CASE WHEN MAXSEQ>=1 THEN SUM(CASE WHEN SEQ<=1 THEN COLLEN ELSE 0 END)
+MAX(CASE WHEN SEQ<=1 THEN SEQ ELSE 0 END) ELSE 0 END pos_1,
CASE WHEN MAXSEQ>=2 THEN SUM(CASE WHEN SEQ<=2 THEN COLLEN ELSE 0 END)
+MAX(CASE WHEN SEQ<=2 THEN SEQ ELSE 0 END) ELSE 0 END pos_2,
CASE WHEN MAXSEQ>=3 THEN SUM(CASE WHEN SEQ<=3 THEN COLLEN ELSE 0 END)
+MAX(CASE WHEN SEQ<=3 THEN SEQ ELSE 0 END) ELSE 0 END pos_3,
CASE WHEN MAXSEQ>=4 THEN SUM(CASE WHEN SEQ<=4 THEN COLLEN ELSE 0 END)
+MAX(CASE WHEN SEQ<=4 THEN SEQ ELSE 0 END) ELSE 0 END pos_4,
CASE WHEN MAXSEQ>=5 THEN SUM(CASE WHEN SEQ<=5 THEN COLLEN ELSE 0 END)
+MAX(CASE WHEN SEQ<=5 THEN SEQ ELSE 0 END) ELSE 0 END pos_5,
CASE WHEN MAXSEQ>=6 THEN SUM(CASE WHEN SEQ<=6 THEN COLLEN ELSE 0 END)
+MAX(CASE WHEN SEQ<=6 THEN SEQ ELSE 0 END) ELSE 0 END pos_6,
CASE WHEN MAXSEQ>=7 THEN SUM(CASE WHEN SEQ<=7 THEN COLLEN ELSE 0 END)
+MAX(CASE WHEN SEQ<=7 THEN SEQ ELSE 0 END) ELSE 0 END pos_7,
CASE WHEN MAXSEQ>=8 THEN SUM(CASE WHEN SEQ<=8 THEN COLLEN ELSE 0 END)
+MAX(CASE WHEN SEQ<=8 THEN SEQ ELSE 0 END) ELSE 0 END pos_8,
CASE WHEN MAXSEQ>=9 THEN SUM(CASE WHEN SEQ<=9 THEN COLLEN ELSE 0 END)
+MAX(CASE WHEN SEQ<=9 THEN SEQ ELSE 0 END) ELSE 0 END pos_9
FROM
(SELECT MAX(SEQ) OVER () MAXSEQ, SEQ, COLLEN
FROM
(SELECT
ROW_NUMBER() OVER (ORDER BY COL) SEQ,
ISNULL(LEN(x.COL.value('./text()[1]','varchar(10)')),0) COLLEN
FROM a.string_xml.nodes('/T') as x(COL)
) b1) b2
GROUP BY MAXSEQ) b;
go

回答No.1

jamshid6

ご参考までに（SQL Sever2005以降）。

もしかしたら参考にならないかもしれませんが、データセットアプローチにこだわって、１クエリで実現する方法としてはこのようなやり方もあります。
（一言でいうと、文字列をXML化した上でテーブルにし、長さを集計する処理をCROSS APPLYで各行に対して実行します）

SELECT
a.string_char,
b.*
FROM (
SELECT
string_char,
CONVERT(xml,'<T>'+REPLACE(string_char,'<','</T><T>')+'</T>') string_xml
FROM @S) a
CROSS APPLY
(SELECT
CASE WHEN MAXSEQ>=1 THEN SUM(CASE WHEN SEQ<=1 THEN COLLEN ELSE 0 END)
+MAX(CASE WHEN SEQ<=1 THEN SEQ ELSE 0 END) ELSE 0 END pos_1,
CASE WHEN MAXSEQ>=2 THEN SUM(CASE WHEN SEQ<=2 THEN COLLEN ELSE 0 END)
+MAX(CASE WHEN SEQ<=2 THEN SEQ ELSE 0 END) ELSE 0 END pos_2,
CASE WHEN MAXSEQ>=3 THEN SUM(CASE WHEN SEQ<=3 THEN COLLEN ELSE 0 END)
+MAX(CASE WHEN SEQ<=3 THEN SEQ ELSE 0 END) ELSE 0 END pos_3,
CASE WHEN MAXSEQ>=4 THEN SUM(CASE WHEN SEQ<=4 THEN COLLEN ELSE 0 END)
+MAX(CASE WHEN SEQ<=4 THEN SEQ ELSE 0 END) ELSE 0 END pos_4,
CASE WHEN MAXSEQ>=5 THEN SUM(CASE WHEN SEQ<=5 THEN COLLEN ELSE 0 END)
+MAX(CASE WHEN SEQ<=5 THEN SEQ ELSE 0 END) ELSE 0 END pos_5,
CASE WHEN MAXSEQ>=6 THEN SUM(CASE WHEN SEQ<=6 THEN COLLEN ELSE 0 END)
+MAX(CASE WHEN SEQ<=6 THEN SEQ ELSE 0 END) ELSE 0 END pos_6,
CASE WHEN MAXSEQ>=7 THEN SUM(CASE WHEN SEQ<=7 THEN COLLEN ELSE 0 END)
+MAX(CASE WHEN SEQ<=7 THEN SEQ ELSE 0 END) ELSE 0 END pos_7,
CASE WHEN MAXSEQ>=8 THEN SUM(CASE WHEN SEQ<=8 THEN COLLEN ELSE 0 END)
+MAX(CASE WHEN SEQ<=8 THEN SEQ ELSE 0 END) ELSE 0 END pos_8,
CASE WHEN MAXSEQ>=9 THEN SUM(CASE WHEN SEQ<=9 THEN COLLEN ELSE 0 END)
+MAX(CASE WHEN SEQ<=9 THEN SEQ ELSE 0 END) ELSE 0 END pos_9
FROM
(SELECT MAX(SEQ) OVER () MAXSEQ, SEQ, COLLEN
FROM
(SELECT
ROW_NUMBER() OVER (ORDER BY COL) SEQ,
ISNULL(LEN(x.COL.value('./text()[1]','varchar(10)')),0) COLLEN
FROM a.string_xml.nodes('/T') as x(COL)
) b1) b2
GROUP BY MAXSEQ) b

補足

jamshid6様
いつも毎度ご迷惑かけております。
質問なのですが、

１．sample_tblはどこに指定すればよいでしょうか？
つまり通常は　from sample_tbl とどこかで指定しないとならないと
思いますが、スクリプトに見当たりません。

２．とりあえず、実行したのですが、

メッセージ 1087、レベル 15、状態 2、行 8
テーブル変数 "@S" を宣言してください。
メッセージ 102、レベル 15、状態 1、行 37
'b' 付近に不適切な構文があります。

と出てきてしまいます。私の小さな理解力では@~は確か
ストアドではDeclareでやっていたような気がします。しかし、今回は
ストアドではなさそうなので余計に不明だらけになっております。

何卒、ご教示よろしくお願いいたします。