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
- What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical SQL questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our SQL guru is waiting to answer your technical questions.