Grouping and formatting output from a select

I'm trying to select a list of different documents - documents in one table, document categories in another - and organize the list by categories.  I've been looking around for a solution but haven't really found anything that does what I'm looking for. I'm probably not using the right terms or perhaps it can't be done.  Either way, I hope I can get some input here.

I'm including a file that has two tiny examples for tables and some data - some 4 records in total - the real data could have up to 100 documents across multiple document categories - but this should illustrate the point of the output format I would like to get.

Activity Forms
  Permission form
  Activity Guide

Sales Forms
  Sales Permits
  Product List

Donation Forms

Volunteer Forms

As I said, I've no idea if this is possible or not - I've been told it is, but so far, I've not figured it out.  As always, many thanks in advance for your time.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You are displaying a 'hierarchy' in the question, SQL would output something like this:

Activity Forms    Permission form
Activity Forms    Activity Guide
Sales Forms         Sales Permits
Sales Form           Product List
Donation Forms  Flyer
Volunteer Forms FAQ

SQL outputs matrices - not hierarchies - so there are 2 'issues'
1. how to provide the data from SQL
2. how to present that data in the desired format

What method/tools are being used to present that data?
e.g. a web page or a report
if a web page are you using PHP? C#? .Net?
if a report, is it something like Crystal Reports?

SQL, by itself, isn't a sophisticated report tool

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Here is your sample data in use:
          , testDoc.docName
    FROM testDoc
    INNER JOIN testCats ON testDoc.catID = testCats.catID
          , testDoc.docName

    |         CATNAME |         DOCNAME |
    |  Activity Forms |  Activity Guide |
    |  Activity Forms | Permission Form |
    |  Donation Forms |           Flyer |
    |      Sales Form |    Product List |
    |      Sales Form |    Sales Permit |
    | Volunteer Forms |             FAQ |


Open in new window

The output you see above is very typical of SQL
saabStoryAuthor Commented:
I wish I was using php or .net - if so, I wouldn't be asking the question.  I am using .net to a point but it's wrapped up the DotNetNuke CMS and, for a variety of reasons, foremost that we're moving to a new platform in a few months, we're not spending the development time on the old system.  

We do have a module in place that lets us query the database and format the results and that's what I've been doing - not the most efficient solution but with the tiny record sets we use, it works okay for the short term.  But, for it to work in this case, I'd have to be able to assign the catName to a variable and then check the variable when the next row came in and change heading with the catName changed - I didn't see how I could to that - maybe like a pivot table - but with a more vertical layout than you typically see - but it doesn't look like it in this instance.  

Many thanks for the feedback -
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.