• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

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.
  • 2
1 Solution
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
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 |

  [1]: http://sqlfiddle.com/#!3/f4d50/4

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 -
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now