Oracle中使用sys_connect_by_path函数实现行转列

表结构及数据展现

[img]http://dl.iteye.com/upload/picture/pic/105142/d406a37f-3be6-3bb9-ad04-e3f67a0cc9e6.png[/img]

要实现的效果

[img]http://dl.iteye.com/upload/picture/pic/105140/beaf97de-a28f-31a4-ad0f-c2aa3c4ff37b.png[/img]

实现sql：

select booktype ,MAX(sys_connect_by_path(t.bookname, '')) as bookname from(
select a.booktype,a.bookname,row_number() over(PARTITION BY booktype ORDER BY bookname) as rn from a
) t
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1
AND booktype = PRIOR t.booktype
GROUP BY t.booktype