상관관계 SQL(PIVOT 사용법)

월간 마이크로소프트웨어 7월호 발췌

MSSql의 경우..

http://blogs.msdn.com/b/spike/archive/2009/03/03/pivot-tables-in-sql-server-a-simple-sample.aspx

select * from DailyIncome
pivot (
sum(IncomeAmount) for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])
) as pvt

VendorId MON TUE WED THU FRI SAT SUN

FREDS 500 700 500 800 900 500 400

JOHNS 300 600 900 800 300 800 600

SPIKE       1200 300 1000 300 800 100 400

<<Data 원본>>

select * from DailyIncome;

VendorId IncomeDay IncomeAmount

SPIKE         FRI 100

SPIKE         MON 300

FREDS SUN 400

SPIKE         WED 500

SPIKE         TUE 200

Oracle 11g와 다른점;

열로 변환될 값을 [] 사용하여 표시

as pvt 처럼 table alias사용