Posted by Hugo Kornelis on 12/06/05 00:42

On Sun, 04 Dec 2005 17:54:47 -0800, Frank wrote:

>I have a table with the following structure:
>
>main_category| category| sub_category| answer|date
>
>Basically, the data will be along these lines:
>
>Neuro | LOC | Status | answer1|date
>Neuro | LOC | Status | answer2|date
>Neuro | LOC | Status | answer3|date
>Senso| Visi | Clarity | answer1|date
>Senso| Visi | Clarity | answer2|date
>etc...
>I am trying to query the db and present the user with the data in the
>following structure:
>
>Main Category
> Category
> Sub Category
> answer1
> answer2
> answer3
> ...
>
>Main Category
> Category
> Etc...
>
>There are literally 3 dozen main categories, categories, and
>sub-categories each with distinct answers.
>I could really use some help on a query to group the data in this way!
>
>Thanks in advance!!!

Hi Frank,

Since this is purely presentation, it's best to handle this in the
presentation layer. Simply return full rows in the required order; the
front-end has to loop through the results anyway and can perform
formatting as required. Here's a query that will give the raw data in
the proper order:

SELECT main_category, category, sub_category, answer
FROM YourTable
ORDER BY main_category, category, sub_category, answer


If you HAVE to do this server side, you'll need either a cursor or a
self-join. Thr cursor should be based on the query above; the rest is
fairly straight-forward so I'll leave it to you to figure it out.

The set-based version uses a self-join - it might be one of these rare
situations where a cursor-based solution is actually faster. It's also
ugly as hell (I told you that the server is not the ideal place for
presentation, didn't I?):

SELECT CASE WHEN prev.main_category IS NULL
THEN this.main_category + CHAR(13) + CHAR(10)
ELSE ' '
END
+ CASE WHEN prev.category IS NULL
THEN this.category + CHAR(13) + CHAR(10)
ELSE ' '
END
+ CASE WHEN prev.sub_category IS NULL
THEN this.sub_category + CHAR(13) + CHAR(10)
ELSE ' '
END
+ this.answer
FROM YourTable AS this
LEFT JOIN YourTable AS prev
ON prev.main_category + '|' + prev.category + '|'
+ prev.sub_category + '|' + prev.answer =
(SELECT TOP 1 x.main_category + '|' + x.category + '|'
+ x.sub_category + '|' + x.answer
FROM YourTable AS x
WHERE x.main_category + '|' + x.category + '|'
+ x.sub_category + '|' + x.answer
<= this.main_category + '|' + this.category + '|'
+ this.sub_category + '|' + this.answer
ORDER BY x.main_category, x.category,
x.sub_category, x.answer)
ORDER BY this.main_category, this.category,
this.sub_category, this.answer

(completely untested - see www.aspfaq.com/5006 if you prefer a tested
reply)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Back to original message]