转自:http://blog.csdn.net/ziwen00/article/details/7870080
在BBS上面看到一个比较不错的行转列的问题,大致SQL如下:
- CREATE TABLE foo(
- fooid NUMBER(20),
- fooname VARCHAR2(20),
- foobar VARCHAR2(20)
- );
- INSERT INTO foo VALUES('1','张三','a');
- INSERT INTO foo VALUES('1','张三','b');
- INSERT INTO foo VALUES('2','李四','a');
- INSERT INTO foo VALUES('3','王五','a');
- INSERT INTO foo VALUES('3','王五','b');
- INSERT INTO foo VALUES('3','王五','c');
- SELECT * FROM foo;
我当时以为,下面的sql是一个比较不错的答案:
- SELECT foo.fooid,foo.fooname,
- MAX(CASE WHEN foo.foobar='a' THEN foo.foobar END) AS A,
- MAX(CASE WHEN foo.foobar='b' THEN foo.foobar END) AS B,
- MAX(CASE WHEN foo.foobar='c' THEN foo.foobar END) AS C
- FROM foo
- GROUP BY foo.fooid,foo.fooname
- ORDER BY 1;
但是看到别人的答案是这个:
- SELECT FOO.FOOID, FOO.FOONAME, WMSYS.WM_CONCAT(FOO.FOOBAR) BAR
- FROM FOO
- GROUP BY FOO.FOOID, FOO.FOONAME;
于是知道了这个函数,具备行转列的作用,很是不错哦!使用WMSYS.WM_CONCAT 实现行转列。