列转行与行转列

  今天被前台开发员问到这方面的问题，因为公司都是用11g，所以想到了pivot与unpivot，一时
把用法忘了，在此记录备忘：

  PS：如果数据库提供了相关功能，没必要再让开发员去写java代码啥的。

unpivot示例：

WITH
---------------------------

t1 AS
 (SELECT 1 col1,
         2 col2,
         3 col3
  FROM dual
  UNION ALL
  SELECT 4,
         5,
         6
  FROM dual
  UNION ALL
  SELECT 7,
         8,
         9
  FROM dual
  UNION ALL
  SELECT 10,
         NULL,
         12
  FROM dual),
---------------------------

t2 AS
 (SELECT * FROM t1 WHERE t1.col1 = 1)
---------------------------

SELECT cval,
       nvl(to_char(cname),
           '-') cname
FROM t2 unpivot include NULLS(cname FOR cval IN(col1 AS 'col1',
                                                  col2 AS 'col2',
                                                  col3 AS 'col3'));

可参考的资料：