行列入れ替え

(*)数値項目を合算して列に～ならPIVOT句とかを使えば良いです。

以下は、非数値項目の例

⇒https://yyuuiikk.org/entry/590

■以下の様なテーブルの行列を入れ替える

〇パターン1

　・キー(AgmtId)だけを抽出したテーブル(AgmtFamily A)に対して、

　　キーと各変換対象の列を個別に抜き出したテーブルを結合する例

　・上記でいうと、FamilyIdの列数が決め打ちである場合に可能

SELECT DISTINCT A.agmtid,F1,姓1,名1,続柄1,F2,姓2,名2,続柄2,F3,姓3,名3,続柄3,F4,姓4,名4,続柄4 FROM AgmtFamily A
left join ( SELECT Agmtid, familyid AS F1,FamilySei AS '姓1',FamilyName AS '名1',TsuzukiGara AS '続柄1' FROM AgmtFamily WHERE familyId = 1) B1 ON A.agmtid = B1.agmtid
left join ( SELECT Agmtid, familyid AS F2,FamilySei AS '姓2',FamilyName AS '名2',TsuzukiGara AS '続柄2' FROM AgmtFamily WHERE familyId = 2) B2 ON A.agmtid = B2.agmtid
left join ( SELECT Agmtid, familyid AS F3,FamilySei AS '姓3',FamilyName AS '名3',TsuzukiGara AS '続柄3' FROM AgmtFamily WHERE familyId = 3) B3 ON A.agmtid = B3.agmtid
left join ( SELECT Agmtid, familyid AS F4,FamilySei AS '姓4',FamilyName AS '名4',TsuzukiGara AS '続柄4' FROM AgmtFamily WHERE familyId = 4) B4 ON A.agmtid = B4.agmtid
ORDER BY a.AgmtId

〇パターン2

単純に1項目での列への変換なら以下(例えば、FamilyIdを列に変換)

select
Agmtid,
sum(case when familyid = 1 then 1 else '' END) as F1,
sum(case when familyid = 2 then 2 else '' END) as F2,
sum(case when familyid = 3 then 3 else '' END) as F3,
sum(case when familyid = 4 then 4 else '' END) as F4
from agmtfamily
group by AgmtId

Family有無を〇に置き換えるなら、上記を更にCaseで分岐
select
Agmtid,
CASE WHEN(sum(case when familyid = 1 then 1 else '' END)) =0 then '×' ELSE '〇' END as F1,
CASE WHEN(sum(case when familyid = 2 then 2 else '' END))=0 then '×' ELSE '〇' END as F2,
CASE WHEN(sum(case when familyid = 3 then 3 else '' END))=0 then '×' ELSE '〇' END as F3,
CASE WHEN(sum(case when familyid = 4 then 4 else '' END))=0 then '×' ELSE '〇' END as F4
from agmtfamily
group by AgmtId

⇒以下のようにする方法も

 (複数列の入れ替えも可能なパターン)

yyuuiikk.org