You can find a solution below. I have some basic knowledge in Oracle and SQL but my html/css skills are very limited. As a reference I used Cascading Style Sheets, level 2 CSS2 Specification. Also to make the table looking nice I applied the basic color matching methods described in Wonderful Web Colors document.
SPOOL tree.html
SET TRIMSPOOL ON TAB OFF FEED OFF HEAD OFF PAGES 50000
SELECT CASE
WHEN o - LAG(o) OVER (ORDER BY o) = 1
THEN '</tr><tr>'
END
|| html html
FROM (
SELECT 1 o
, 0 r
, '<html><head>'
|| '<title>Vladimir''s diary: SQL Puzzle IV: Tree</title>'
|| '<style type="text/css">'
|| 'table {'
|| ' width: 25%'
|| '}'
|| 'td {'
|| ' text-align: center;'
|| ' text-valign:middle;'
|| ' font-size: 8pt;'
|| ' padding: 2px;'
|| ' border: 2px #998 solid;'
|| ' background: #bba;'
|| '}'
|| '</style></head>'
|| '<body>'
|| '<table><tr>' html
FROM dual
UNION ALL
SELECT LEVEL
, ROWNUM
, '<td rowspan="'
|| CASE
WHEN EXISTS (
SELECT null
FROM emp dd
WHERE dd.mgr = m.empno
AND ROWNUM < 2
)
THEN 1
ELSE mx.lvl - LEVEL
END
|| '" colspan="'
|| (
SELECT COUNT(*)
FROM emp
WHERE CONNECT_BY_ISLEAF = 1
START WITH empno = m.empno
CONNECT BY PRIOR empno = mgr
)
|| '">'
|| '<b>'
|| ename
|| '</b>'
|| '<br>'
|| LOWER(job)
|| '</td>'
FROM emp m
, (
SELECT MAX(LEVEL) + 1 lvl
FROM emp mx
START WITH mx.mgr IS NULL
CONNECT BY PRIOR mx.empno = mx.mgr
) mx
START WITH m.mgr IS NULL
CONNECT BY PRIOR m.empno = m.mgr
UNION ALL
SELECT NULL
, NULL
, '</tr></table>'
|| '</body></html>'
FROM dual
)
ORDER BY
o
, r
/
SPOOL OFF
No comments:
Post a Comment