以Oracle數據庫為例，測試在數據處理中經常遇到數據庫中橫表和豎表的相互轉換，采用學生各科成績分數為測試數據

橫表轉換豎表:

CREATE table score(   username varchar(10) ,   chinese  int ,   math     int ,   physics  int); INSERT into score(username , chinese , math , physics) values('Kay',74,83,93);INSERT into score(username , chinese , math , physics) values('Jim',75,84,94);INSERT into score(username , chinese , math , physics) values('Lily',86,49,91); COMMIT; SELECT * FROM score;
橫表轉換成豎表關鍵在于如何把一條記錄轉換成多條記錄，三個分數字段對應三條記錄，可以采用一個只有三條記錄中間表和score表做笛卡爾積把一條記錄copy成三條記錄
WITH T AS( SELECT 'chinese' course FROM dual  UNION ALL  SELECT 'math'    course FROM dual  UNION ALL   SELECT 'physics' course FROM dual) SELECT username,course,decode(course,'chinese',chinese,'math',math,'physics',physics)   FROM score ,T  ORDER BY username ,course;豎表轉換橫表：
SELECT * FROM course;
豎表轉換橫表關鍵在于如何把多條記錄合并成一條記錄，按照username進行group by 使用聚合函數把多條合并成一條且按不同的類別值合并到不同的字段
SELECT username,        MAX(decode(course,'chinese',score)) chinese,       MAX(decode(course,'math',score)) math,       MAX(decode(course,'physics',score)) physics  FROM courseGROUP BY usernameORDER BY username