Problem solve Get help with specific problems with your technologies, process and projects.

Denormalizing a result set

I have a table design question. Here is my table:

dept_no   job
=======   ====
1         clerk
1         account
1         sales
2         engineer
2         manager

How can I group the dept_no and show the jobs in each department? For example,

dept_no         ALL job in Dept
=======         ===============
1               clerk account sales
2               engineer manager

There is no simple way (that I can think of) to do this with straight SQL. There may be a very complex way, involving an identity matrix of job codes, but it would be slow and ugly, and you would still have to process the output somehow.

Your best bet is to handle this type of formatting in whatever report writer, server script, or programming language you are using.

For example, in Cold Fusion, you would retrieve the records from the table like this --

  select dept_no, job
    from theTable
   order by
         dept_no, job


Assuming you wanted to format the output into an HTML table, you would generate it like this --

<!-- start the table -->
<table border="1" cellpadding="8">
<!-- heading row -->
<tr><th>Dept</th><th>Jobs in
<CFOUTPUT QUERY="getData" GROUP="dept_no">
<!-- each dept gets a new row -->
<!-- start the job cell -->
<!-- output each job into this cell -->
<!-- close the job table cell and row -->
<!-- close the table -->

This would result in --

Dept Jobs in Dept
1 clerk account sales
2 engineer manager

For More Information

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.