行转列应该是数据库比较常见的操作了，在oracle中可以使用pivot、decode
SELECT name, MAX(DECODE(course, 'java', gread)) AS java, MAX(DECODE(course, 'c#', gread)) AS c#, MAX(DECODE(course, 'c', gread)) AS c, MAX(DECODE(course, 'sql', gread)) AS sqlFROM tGROUP BY name;NAME JAVA C# C SQL---------- ---------- ---------- ---------- ----------dai 60 70 80 90tu 90 80 70 60
实际写报表时遇到一个变异的行转列要求：
数据列pocket_ID,Serial_Number大约查到50多行记录:
oracle账号要求按照pocket_ID的顺序将serial_Number 按照每行8个数据的格式写出来，即
与呆瓜中提到的例子相比，因为没有一个合适的group字段，所以在将阶梯表转为报表时稍微费了点心思，添加了一个辅助字段实现了所需功能：
select max(A) as A,max(B) as B,max(C) as C,max(D) as D,max(E) As E,max(F) As F,max(G) as G,Max(H) as H from (SELECT trunc((P.POCKET_ID-1)/8,0) as RM, DECODE(MOD(POCKET_ID, 8), 1, SERIAL_NUMBER) AS A, DECODE(MOD(POCKET_ID, 8), 2, SERIAL_NUMBER) AS B, DECODE(MOD(POCKET_ID, 8), 3, SERIAL_NUMBER) AS C, DECODE(MOD(POCKET_ID, 8), 4, SERIAL_NUMBER) AS D, DECODE(MOD(POCKET_ID, 8), 5, SERIAL_NUMBER) AS E, DECODE(MOD(POCKET_ID, 8), 6, SERIAL_NUMBER) AS F, DECODE(MOD(POCKET_ID, 8), 7, SERIAL_NUMBER) AS G, DECODE(MOD(POCKET_ID, 8), 0, SERIAL_NUMBER) AS H FROM 数据表 WHERE 1 = 1 and 其他条件ORDER BY POCKET_ID asc) Mgroup by M.RM order by RM asc
查询结果: