縦横を入れ替えるテーブル2つ使ったSQLを教えてください

coconan
2014-06-25 19:00 1835 3
縦横を入れ替えるテーブル2つ使ったSQLを教えてください
さっそくですが、
SQLSERVERにて
・店舗マスタ
ID｜名前
ーーーー
01｜A
15｜D
22｜E
50｜F
65｜G

・会員テーブル
ID｜ナンバー
ーーーー
A1｜01
A1｜22
A1｜65
A2｜15
A2｜22

という２つのテーブルが有る場合に、
ID｜A｜D｜E｜F｜G｜
ーーーーーーーーーーー
A1｜1｜0｜1｜0｜1｜
A2｜0｜1｜1｜0｜0｜

という風に存在するナンバーに1を立てた
縦横を入れ替えた出力を行うSQLは作れますでしょうか。
出力カラムを店舗マスタの名前にしていますが、ここは店舗マスタのIDであっても構いません。

条件としまして。
・DBはSQLServerを使用します。
・店舗マスタは件数が変動し、固定ではありません。（ただし、最大で10個まで）
・会員テーブルも件数が変動します。（上限無し）
・出力カラムを店舗マスタの名前でも店舗マスタのIDでも構いません。（名前の方が嬉しいです）
・店舗マスタのID 会員テーブルのIDとナンバーはキーとなっており重複することはありません。（他にキーはありません。）

調べてPIVOTというのを使うと良いみたいなのですが、良く分からなくて。。
ご指導よろしくお願いします。

コメント

2014年 06月 27日 (11:55)
こんにちは。
イノ(inno)です。

方法はいろいろありますけど、
私は下記のように作成してみました。
下記のSQLをそのまま実行してみてください。
先ずは、テーブルを作成してください。

--#################################
--#### テーブル作成
--#################################

CREATE TABLE dbo.TempShop
(
ShopID varchar(10)
, ShopName varchar(10)
)

CREATE TABLE dbo.TempUser
(
UserID varchar(10)
, ShopID varchar(10)
)

ここまでがテーブル作成です。
それではTESTデータを登録しましょう。

INSERT INTO dbo.TempShop
SELECT '01', 'A' UNION ALL
SELECT '15', 'D' UNION ALL
SELECT '22', 'E' UNION ALL
SELECT '50', 'F' UNION ALL
SELECT '65', 'G'

INSERT INTO dbo.TempUser
SELECT 'A1', '01' UNION ALL
SELECT 'A1', '22' UNION ALL
SELECT 'A1', '65' UNION ALL
SELECT 'A2', '15' UNION ALL
SELECT 'A2', '22'

ここまでがTESTデータ登録です。
これで準備が整いました。
では、下記のSQLを実行すると

ID｜A｜D｜E｜F｜G｜
ーーーーーーーーーーー
A1｜1｜0｜1｜0｜1｜
A2｜0｜1｜1｜0｜0｜

の結果が出ると思います。
店舗マスタ,会員テーブルは件数が変動しても下記のSQLなら問題ありません。
では、下記のSQLを実行してみましょう。

DECLARE @StrSQL_Col nvarchar(max)
SET @StrSQL_Col = N''

SELECT @StrSQL_Col = @StrSQL_Col + N', SUM(CASE WHEN ShopID = ''' + ShopID + ''' THEN 1 ELSE 0 END) AS [' + ShopName + ']'
FROM dbo.TempShop

DECLARE @StrSQL nvarchar(max)
SET @StrSQL = N'SELECT UserID '
SET @StrSQL = @StrSQL + @StrSQL_Col
SET @StrSQL = @StrSQL + N' FROM dbo.TempUser GROUP BY UserID '

EXEC (@StrSQL)

ここまで実行してください。
どうですか？
結果が正常にでましたか？

上記のSQLは内部的に下記のようなSQLで実行されていますので、ご参考ください。

SELECT UserID
, SUM(CASE WHEN ShopID = '01' THEN 1 ELSE 0 END) AS [A]
, SUM(CASE WHEN ShopID = '15' THEN 1 ELSE 0 END) AS [D]
, SUM(CASE WHEN ShopID = '22' THEN 1 ELSE 0 END) AS [E]
, SUM(CASE WHEN ShopID = '50' THEN 1 ELSE 0 END) AS [F]
, SUM(CASE WHEN ShopID = '65' THEN 1 ELSE 0 END) AS [G]
FROM dbo.TempUser
GROUP BY UserID
coconan 2014年 06月 27日 (18:13)
ご返答ありがとうございます。
提案していただいたのですが、
ストアドプロシージャを使わないでは無理でしょうか。
SQLのselect文だけで出来たらしたいです。
2014年 06月 30日 (16:07)
そうですね。SQL文だけではちょっと難しいですね。
下記はPIVOTを利用したものですが、下記のように店舗名を全部並べてするか、

SELECT UserID
, ISNULL([A],0) [A]
, ISNULL([B],0) [B]
, ISNULL([C],0) [C]
, ISNULL([D],0) [D]
, ISNULL([E],0) [E]
, ISNULL([F],0) [F]
, ISNULL([G],0) [G]
FROM
(
SELECT A.UserID, B.ShopName, COUNT(A.ShopID) Cnt
FROM dbo.TempUser A JOIN dbo.TempShop B
ON A.ShopID = B.ShopID
GROUP BY A.UserID, B.ShopName
) A PIVOT
(
SUM(A.Cnt) FOR A.ShopName IN ([A],[B],[C],[D],[E],[F],[G])
) AS Pv1

もしくは、

SELECT UserID
, SUM(CASE WHEN ShopID = '01' THEN 1 ELSE 0 END) AS [A]
, SUM(CASE WHEN ShopID = '15' THEN 1 ELSE 0 END) AS [D]
, SUM(CASE WHEN ShopID = '22' THEN 1 ELSE 0 END) AS [E]
, SUM(CASE WHEN ShopID = '50' THEN 1 ELSE 0 END) AS [F]
, SUM(CASE WHEN ShopID = '65' THEN 1 ELSE 0 END) AS [G]
FROM dbo.TempUser
GROUP BY UserID

こういう風にCASE WHENを利用して店舗名を全部並べてするか、
もしくは！！！

上記で動的SQLで説明しました下記のSQLを。。


DECLARE @StrSQL_Col nvarchar(max)
SET @StrSQL_Col = N''

SELECT @StrSQL_Col = @StrSQL_Col + N', SUM(CASE WHEN ShopID = ''' + ShopID + ''' THEN 1 ELSE 0 END) AS [' + ShopName + ']'
FROM dbo.TempShop

DECLARE @StrSQL nvarchar(max)
SET @StrSQL = N'SELECT UserID '
SET @StrSQL = @StrSQL + @StrSQL_Col
SET @StrSQL = @StrSQL + N' FROM dbo.TempUser GROUP BY UserID '

EXEC (@StrSQL)

SQL側ではなく、プログラム側で変換して実行するのはいかがでしょうか。

SELECT @StrSQL_Col = @StrSQL_Col + N', SUM(CASE WHEN ShopID = ''' + ShopID + ''' THEN 1 ELSE 0 END) AS [' + ShopName + ']'
FROM dbo.TempShop

この「@StrSQL_Col」部分は。。
, SUM(CASE WHEN ShopID = '01' THEN 1 ELSE 0 END) AS [A]
, SUM(CASE WHEN ShopID = '15' THEN 1 ELSE 0 END) AS [D]
, SUM(CASE WHEN ShopID = '22' THEN 1 ELSE 0 END) AS [E]
, SUM(CASE WHEN ShopID = '50' THEN 1 ELSE 0 END) AS [F]
, SUM(CASE WHEN ShopID = '65' THEN 1 ELSE 0 END) AS [G]
これを作るためのSQLです。

あれをプログラム側で上記のような文字列を作るようにコーディングして
@StrSQL_Col みたいな変数に保存して

SELECT UserID ' + @StrSQL_Col + ' FROM dbo.TempUser GROUP BY UserID

このSQLを実行するだけで結果が出ます。
ストアドプロシージャの作成も必要なくなります。
どうでしょうか。
SQL文で２つのSELCT文の結果を繋げる方法はありますでしょうか。
2014-07-23 1490 1
日時のデータ型について
2014-07-23 1486 1
SQLServer で検索した値を使って計算を行いUPDATEしたい
2014-07-22 1498 1
1つのテーブル・2つの列を結合して１つの検索結果にする方法を教えてください。
2014-07-14 1465 1
sqlserverで書式チェックしたい
2014-07-09 1572 1
既存列にデフォルト値を設定する方法
2014-06-30 1555 1
縦横を入れ替えるテーブル2つ使ったSQLを教えてください
2014-06-25 1835 3
VBAを利用したテーブル名とフィールド名一覧取得
2014-06-12 2093 1
MS SQL Serverでのクエリ文
2014-06-05 1654
SQLServerのバックアップ
2014-05-28 1714 1