Tuesday, November 15, 2005

SQL puzzle IV.

Here is yet another one. I would like to have this table generated by a SQL statement.

htree
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: