横持ちのフラグ立て

質問

はじめまして。いろいろとSQL文の参考になる情報を探したのですが、なかなか見つからなくて困っております。
以下のテーブルがあったとします。
id,product
1,缶ビール
1,缶ビール
1,犬
1,猫
2,猫
2,犬
2,ロケット
3,ロケット
3,象さん
3,くま
3,くま

上記の取引データを以下のようにしたいのです。
id,くま,ロケット,犬,猫,缶ビール,象さん
1,T,F,T,T,T,F
2,F,T,T,T,F,F
3,T,T,F,F,F,T

productフィールドにはアンケートの解答で、該当するproductフィールドに解答したら'T'、ないなら'F'なのです。私のSQLの能力では
CASE WHENを多用しフィールド名（くま、ロケットなど）をすべて打たないとなりませんが、およそ600フィールド程度あります。これをなんとか600フィールドを打たないように簡潔にできないものでしょうか？
フィールド名をデータから取得できれば問題ないのですが。どうかよろしくお願いいたします。

回答No.4

jamshid6 · ベストアンサー

この機会に勉強されるというのはいいことです。
また、SELECT * INTO ... FROM ...の認識もあっています。

ポイントは今回ご紹介したものは、
1) まずはSELECT文を組み上げる（動的クエリとして）
2) 実行する
という構造になっていることです。
INTO句は、SELECT文を組み上げる過程ではなく、組み上げたSELECT文に必要なのです。
INTO句を組み込む場所は以下の通りです。

DECLARE @sql nvarchar(max);
SELECT @sql=ISNULL(@sql+',','SELECT ID,')
+'ISNULL(MAX(CASE WHEN product='''+product+''' THEN ''T'' END),''F'') ['+product+']'
FROM アンケートの回答テーブル
GROUP BY product;
SET @sql=@sql+' INTO 横持ちテーブル FROM アンケートの回答テーブル GROUP BY ID';
EXEC (@sql);

お礼

できました！本当にありがとうございます！
今日、本屋に行って「やさしいT-SQL入門」のストアド部分から目を通していました。実はx64上で動くvmware上のxp仮想クライアントにSQL2008devをいれてあれこれ習得＆テストしてから月曜に会社で作業をしようとしてました。とてもIntegrationServiceでできるようなものでなかったです。動的にできるなんて目からウロコです。select~whereなど初心者並にＳＱＬ言語は多少分かるのですが、今回のようなパターンだとどうにもならなかったのです。普段は統計アナリストとしてＳＡＳなどの統計解析ツールを使うのですが、今回はシステムの都合上、ＤＢエンジンで処理をしたかったのです。なんとか習得して私も回答できるようになりたいと思います。ありがとうございました！

回答No.3

jamshid6

>元々ありません。つまり、「アンケートの回答テーブル」のみから以下のテーブルを自動で作り出したいのです。
そういうことですか。であれば想定されていても誰も回答していない項目は列として表示されませんが、それでいいということですね。

であれば、２パターンとも書いておくと、以下の通りです。
DECLARE @sql nvarchar(max);
SELECT @sql=ISNULL(@sql+',','SELECT ID,')
+'ISNULL(MAX(CASE WHEN product='''+product+''' THEN ''T'' END),''F'') ['+product+']'
FROM アンケートの回答テーブル
GROUP BY product;
SET @sql=@sql+'FROM アンケートの回答テーブル GROUP BY ID';
EXEC (@sql);

DECLARE @sql nvarchar(max);
SELECT @sql=ISNULL(@sql+',','SELECT ID,')
+'SUM(CASE WHEN product='''+product+''' THEN 1 ELSE 0 END) ['+product+']'
FROM アンケートの回答テーブル
GROUP BY product;
SET @sql=@sql+'FROM アンケートの回答テーブル GROUP BY ID';
EXEC (@sql);

なお、SQL Server 2005以降であれば問題ありません。SQL Server2000までは「varchar(max)」という型がなく、最長が8000バイトだったため、「600カラムもあると組み上げたクエリが8000バイトを超えてしまう」から少し厄介だと書いたわけです。

補足

jamshid6様

重ね重ねのご回答、誠にありがとうございます。無事、
出力ができるようになりました。本当にありがとうございます。
ところで、出力を別テーブル（例：横持ちテーブル）に保存しようとして以下のようにINTO 「横持ちテーブル」を入れたのです。

DECLARE @sql nvarchar(max);
SELECT @sql=ISNULL(@sql+',','SELECT ID,')
+'ISNULL(MAX(CASE WHEN product='''+product+''' THEN ''T'' END),''F'') ['+product+']'
INTO 横持ちテーブル
FROM アンケートの回答テーブル
GROUP BY product;
SET @sql=@sql+'FROM アンケートの回答テーブル GROUP BY ID';
EXEC (@sql);

エラー内容は以下の通りです。
「メッセージ 194、レベル 15、状態 1、行 2
SELECT INTO ステートメントに、変数に値を代入する SELECT ステートメントを含めることはできません。」

私の浅い知識ではSQL2005では
select * into 新しいテーブル from 元テーブル
なのですが、なぜかできません。どのようにしたら
新しいテーブルに出力を保存できますでしょうか？

T-SQLになると調べても難しくて手がでません。
申し訳ありませんが、どうかご教授ください。
（これを機会に勉強します。）

回答No.2

jamshid6

#1です。
どこにも無限ループになる要因はありませんけどね。
私の理解が正しければ、「くま、ロケットなど600フィールドあるテーブル」が「アンケートの回答テーブル」以外にあるんですよね？

まずは、SSMS(SQL Server Management Stugio)で実行してみてください。
そのときに、最後の１文はEXEC (@sql)ではなく、print @sqlに変更します。実行前の生成されたSQL文が見られます。
SELECT ID,
ISNULL(MAX(CASE WHEN product='くま' THEN 'T' END),'F') [くま],
ISNULL(MAX(CASE WHEN product='ロケット' THEN 'T' END),'F') [ロケット],
........
FROM (アンケートの結果テーブル)
GROUP BY ID
こんなSQL文が生成されていないならば、どこかに誤りがあります。

補足

jamshid6様
お手数おかけしまして申し訳ありません。私の勘違いで
runではなくデバッグをしてました。なお、ＤＢはSQL2008です。
徹夜明けで頭がもうろうでｈした。何を実行してるか分からず頭飛んでました。

それと誤解があるので、修正させて頂きたいと思います。
「くま、ロケットなど600フィールドあるテーブル」は
元々ありません。つまり、「アンケートの回答テーブル」のみから
以下のテーブルを自動で作り出したいのです。
id　くま　ロケット　犬　猫　缶ビール　象さん
1　T　F　T　T　T　F
2　F　T　T　T　F　F
3　T　T　F　F　F　T
もしくはＩＤごとの集計でもＯＫなのです。むしろこちらのほうが
よいかなとも感じております。
id　くま　ロケット　犬　猫　缶ビール　象さん
1　0　0 1　1　2 0
2　0　1 1　1　0 0
3　2　1 0　0　0 1

600フィールドとは元々の「アンケートの回答テーブル」からCase whenを使えば「手打ちのＳＱＬでで600フィールド」ぐらいの作業になってしまい手打ちが大変だという意味で書いてしまいましたのでお忘れください。誤解を生んですみませんでした。元々はないのです。

考えていたのは「アンケートの回答テーブル」の中の「くま」「犬」などのデータをそのままフィールド名として取得できれば最高なんです。
元々は心理解析を専門としており、SASという統計解析ツールでは
転置行列にしてフラグを立ててやってましたが、会社の都合で
SQLで対処せねばなりませんでした。どうかご返答のほど、よろしくお願いいたします。

回答No.1

jamshid6

SQL文１つだけでは無理でしょう。動的クエリでないと実現できないからです。動的クエリでもよいなら、以下の通りできます。
SQL Server2005の例です。SQL Server2000だと少し面倒になります。

DECLARE @sql nvarchar(max);
SELECT @sql=ISNULL(@sql+',','SELECT ID,')
+'ISNULL(MAX(CASE WHEN product='''+name+''' THEN ''T'' END),''F'')'+' ['+name+']'
FROM sys.columns
WHERE OBJECT_ID=OBJECT_ID('600フィールドあるテーブル');
SET @sql=@sql+'FROM アンケートの回答テーブル GROUP BY ID';
EXEC (@sql);

お礼

ありがとうございます。
かなり勉強せねばと感じてます。
なかなかこういうのはサンプルで出てないのでとても勉強になります。
集計でのGroup by なら分かってるのですが、質的なものは
とても初心者のＳＱＬでは難しくて。
本当にありがとうございます！

補足

jamshid6様、
いつも申し訳ありません。実行させてみたのですが、デバッガが終了せず、無限ループに入ってるようです。カンマが問題なんでしょうか？アンケートの回答テーブルはＣＳＶからインポートしたテーブルです。
idとproductフィールドは文字列でカンマはありません。いかに対応すればよいでしょうか？

ID product
1 缶ビール
1 缶ビール
1 犬
1 猫
2 猫
2 犬
2 ロケット
3 ロケット
3 象さん
3 くま
3 くま