Need help on Complex Query that brings back groups and sub-groups
-
Hi all,
I have a situation where I need to return return data from the database in a tree-like manner. For example, I have a table called Questions, where the records contained in the database can either groups or questions, where questions will fall under groups (there is a column QuestionType that determines whether a record is a Question or Group).
So, I need a query that will get my all groups, and their sub-groups (if any) and the Questions that site under these groups (again, if any).
So some the structure of the output from the query should be like the following:
Group 1
----- Sub Group 1
---------- Question 1
---------- Question 2
----- Sub Group 2
---------- Question 1
Group 2
----- Sub Group 1
Group 3
----- Sub Group 1
---------- Question 1
---------- Question 2
---------- Question 3
Question 1
Question 2
Is this possible?
Hope you can help
Thanks
-
Maybe a snapshot of some data may help
QuestTreeUID ShortDesc LongDesc ParentUID QuestNodeUID
14SmartwasteSmartwaste 01
15Skip Skip 143
16Product Product 142
17MaterialMaterial 163
18Product Product 163
19FeedbackFeedback 142
20Origin of wasteOrigin of waste 193
21Cause Cause 193
When Questions with a QuestNodeUID of 1 is the parent (Inspection), QuestNodeTypeUID 2 is of type Group, and 3 is the actual Question. I'd like to format this data into an Output like I provided in my first post through SQL.
Thanks
-
Probably what you need is simple XML out of you data.
_____________
Code for TallyGenerator -
The QuestNodeUID made this a real pain in the hiney... the following is a complete test showing what you want... this has a "limited" range of hierarchies but will probably handle whatever you throw at it...
--===== If the temporary demonstration tables exist, drop them
IF OBJECT_ID('TempDB..#MyHead') IS NOT NULL
DROP TABLE #MyHead
IF OBJECT_ID('TempDB..#Hierarchy') IS NOT NULL
DROP TABLE #Hierarchy
--===== Create the table to hold the data posted for test
CREATE TABLE #MyHead
(
QuestTreeUID INT PRIMARY KEY CLUSTERED,
ShortDesc VARCHAR(20),
LongDesc VARCHAR(20),
ParentUID INT,
QuestNodeUID INT
)
--===== Populate the table with the test data posted
INSERT INTO #MyHead(QuestTreeUID,ShortDesc,LongDesc,ParentUID,QuestNodeUID)
SELECT 14,'Smartwaste','Smartwaste',0,1 UNION ALL
SELECT 15,'Skip','Skip',14,3 UNION ALL
SELECT 16,'Product','Product',14,2 UNION ALL
SELECT 17,'Material','Material',16,3 UNION ALL
SELECT 18,'Product','Product',16,3 UNION ALL
SELECT 19,'Feedback','Feedback',14,2 UNION ALL
SELECT 20,'Origin of waste','Origin of waste',19,3 UNION ALL
SELECT 21,'Cause','Cause',19,3
--===== Test setup complete, we're ready to rock!
--===== Create and preset a level counter.
DECLARE @CurrentLevel INT
SET @CurrentLevel = 0
--===== Create the Hierarchy table
CREATE TABLE #Hierarchy
(
QuestTreeUID INT PRIMARY KEY,
ParentUID INT,
Level INT,
Hierarchy VARCHAR(8000),
QuestNodeUID INT
)
--===== Seed the Hierarchy table with the top level
INSERT INTO #Hierarchy
(QuestTreeUID,ParentUID,Level,Hierarchy,QuestNodeUID)
SELECT QuestTreeUID,
ParentUID,
0 AS Level,
STR(QuestTreeUID,7)+' ' AS Hierarchy,
QuestNodeUID
FROM #MyHead
WHERE ParentUID =0
--===== Determine the rest of the hierarchy
WHILE @@ROWCOUNT > 0
BEGIN
SET @CurrentLevel = @CurrentLevel + 1 --Started at 0
INSERT INTO #Hierarchy
(QuestTreeUID,ParentUID,Level,Hierarchy,QuestNodeUID)
SELECT p.QuestTreeUID,
p.ParentUID,
@CurrentLevel AS Level,
CASE
WHEN p.QuestNodeUID IN (1,2)
THEN h.Hierarchy + STR(p.QuestTreeUID,7)+' '
ELSE h.Hierarchy + STR(9999999,7)
END AS Hierarchy,
p.QuestNodeUID
FROM #MyHead p
INNER JOIN #Hierarchy h
ON p.ParentUID = h.QuestTreeUID
AND h.Level = @CurrentLevel - 1
END
--===== Produce the hierarchical report
SELECT REPLICATE('-----',h.Level)+SPACE(SIGN(h.Level))+ p.ShortDesc
FROM #MyHead p,
#Hierarchy h
WHERE NOT (h.Level = 1 AND h.QuestNodeUID = 3) --Skips out of line entries
AND p.QuestTreeUID = h.QuestTreeUID
ORDER BY h.Hierarchy
Please send beer... I already have enough pretzels
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thank you very much for this Jeff. I forgot about my post, but just remembered it as I need it.
Top stuff
-
Thanks Trystan,
Looking back at it, one of the CASE statements is simply overkill... replacing this part should also work with a tiny bit more speed...
--===== Determine the rest of the hierarchy
WHILE @@ROWCOUNT > 0
BEGIN
SET @CurrentLevel = @CurrentLevel + 1 --Started at 0
INSERT INTO #Hierarchy
(QuestTreeUID,ParentUID,Level,Hierarchy,QuestNodeUID)
SELECT p.QuestTreeUID,
p.ParentUID,
@CurrentLevel AS Level,
h.Hierarchy + STR(p.QuestTreeUID,7)+' ' AS Hierarchy,
p.QuestNodeUID
FROM #MyHead p
INNER JOIN #Hierarchy h
ON p.ParentUID = h.QuestTreeUID
AND h.Level = @CurrentLevel - 1
END
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply