偶遇Oracle行转列

WBOY
WBOYオリジナル
2016-06-07 14:50:191134ブラウズ

行转列应该是数据库比较常见的操作了,在oracle中可以使用pivot、decode,可以参考呆瓜的blog: http://blog.csdn.net/ch7543658/article/details/41146809 SELECT name, MAX(DECODE(course, java, gread)) AS java, MAX(DECODE(course, c#, gread)) AS c#,

行转列应该是数据库比较常见的操作了,在oracle中可以使用pivot、decode,可以参考呆瓜的blog:

http://blog.csdn.net/ch7543658/article/details/41146809

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 sql
FROM t
GROUP BY name;

NAME             JAVA         C#          C        SQL
---------- ---------- ---------- ---------- ----------
dai                60         70         80         90
tu                 90         80         70         60

实际写报表时遇到一个变异的行转列要求:

数据列pocket_ID,Serial_Number大约查到50多行记录:



要求按照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) M
group by M.RM order by RM asc
查询结果:


声明:
この記事の内容はネチズンが自主的に寄稿したものであり、著作権は原著者に帰属します。このサイトは、それに相当する法的責任を負いません。盗作または侵害の疑いのあるコンテンツを見つけた場合は、admin@php.cn までご連絡ください。