Q
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
Dept</th></tr>
<CFOUTPUT QUERY="getData" GROUP="dept_no">
<!-- each dept gets a new row -->
<tr><td>#dept_no#</td>
<!-- start the job cell -->
<td>
<CFOUTPUT>
<!-- output each job into this cell -->
#job#
</CFOUTPUT>
<!-- close the job table cell and row -->
</td></tr>
</CFOUTPUT>
<!-- close the table -->
</table>

This would result in --

Dept Jobs in Dept
1 clerk account sales
2 engineer manager

For More Information


This was last published in June 2001

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close