displaying hierarchical data in one row

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

displaying hierarchical data in one row

Author

Topic

Harry C
Posting Yak Master

148 Posts

Posted - 2010-01-03 : 00:24:43

This is more of a theory question, rather than concrete answers. But any code or examples would be great too.

Lets say I have a table called

Registration
RegID
Name
Event
Date
EventID

And A table called Questions
RegID
QuestionID
Question

And I have a table called RegAnswers
RegAnswerID
QuestionID
Answer

So, in my query I would have something like this...

SELECT r.RegID,r.Name,r.Event,r.Date,q.QuestionID,q.Quest ion,a.Answer FROM Registration r
INNER JOIN Questions q ON q.RegID = r.RegID
INNER JOIN RegAnswers ra ON q.QuestionID = ra.Questions
WHERE EventID = 286

So, in this case, I would have a one to many relationship between Registrations and RegAnswers. One registrant might answer 5 questions. So, when I return the data, I want to return it so that the registrant and questions/answers are in one row.
The thing I want to mention is that there are potentially 7 to 8 THOUSAND rows. Most of the time I will be exporting these to EXCEL or CSV. Any thoughts on an efficient way to handle this? Oh and this is just an example, my actual DB has many more JOINED tables. Thanks

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-01-03 : 01:31:57

can u post some sample data and output.............

Harry C
Posting Yak Master

148 Posts

Posted - 2010-01-04 : 00:15:11

This is a SMALL cross section of my data. I am just trying to get an idea on how this might be accomplished. Thanks for taking a look!

I want to display this on one line

AccountGUID, Date, TotalFee, WaiverInitials, QuestionText, QuestionResponse, QuestionText, QuestionResponse

32CA288C-9174-4591-AEF5-14A70AA70F39,2010-01-03 21:07:07.327,85.00, HC, What are you doing?, Nothing, Do you like water?, No I do not

[CODE]
CREATE TABLE #Registration(
[Guid] [uniqueidentifier] NOT NULL,
[AccountGuid] [uniqueidentifier] NOT NULL,
[RaceGuid] [uniqueidentifier] NOT NULL,
[Date] [datetime] NULL,
[TotalFee] [money] NOT NULL,
[WaiverInitials] [nvarchar](50) NULL,
[OnlineFee] [money] NOT NULL,
[PayByCheck] [bit] NOT NULL,
[IsPaid] [bit] NOT NULL,
)
INSERT INTO #Registration
([Guid]
,[AccountGuid]
,[RaceGuid]
,[Date]
,[TotalFee]
,[WaiverInitials]
,[OnlineFee]
,[PayByCheck]
,[IsPaid])
VALUES
('14daa7e9-2fc5-42db-9265-d662c1cff912'
,'32ca288c-9174-4591-aef5-14a70aa70f39'
,'ff4e9f46-3f4a-42c0-87e8-2dfbd9dcb2c7'
,GETDATE()
,85.00
,'HC'
,5.00
,0
,1)

CREATE TABLE #RegistrationQuestion(
[Guid] [uniqueidentifier] NOT NULL,
[RaceGuid] [uniqueidentifier] NOT NULL,
[RegistrationGuid] [uniqueidentifier] NOT NULL,
[QuestionText] [nvarchar](300) NOT NULL,
[QuestionType] [nvarchar](50) NOT NULL,
[QuestionResponse] [nvarchar](1000) NULL,
[DisplayOrder] [int] NOT NULL,
)

INSERT INTO #RegistrationQuestion
([Guid]
,[RaceGuid]
,[RegistrationGuid]
,[QuestionText]
,[QuestionType]
,[QuestionResponse]
,[DisplayOrder])
VALUES
('13b5dd40-63d8-4cad-8404-037996d81139'
,'ff4e9f46-3f4a-42c0-87e8-2dfbd9dcb2c7'
,'14daa7e9-2fc5-42db-9265-d662c1cff912'
,'What are you doing?'
,'text'
,'Nothing'
,0)

INSERT INTO #RegistrationQuestion
([Guid]
,[RaceGuid]
,[RegistrationGuid]
,[QuestionText]
,[QuestionType]
,[QuestionResponse]
,[DisplayOrder])
VALUES
('049049b0-fdf0-4c7f-a9da-1a462ed208a7'
,'ff4e9f46-3f4a-42c0-87e8-2dfbd9dcb2c7'
,'14daa7e9-2fc5-42db-9265-d662c1cff912'
,'Do you like water?'
,'text'
,'No I do not'
,0)

SELECT r.AccountGuid, r.Date,r.TotalFee,r.WaiverInitials
,ra.QuestionText, ra.QuestionResponse
FROM #RegistrationQuestion ra
INNER JOIN #Registration r ON r.[Guid] = ra.[RegistrationGuid]
WHERE ra.RaceGuid='FF4E9F46-3F4A-42C0-87E8-2DFBD9DCB2C7'

DROP Table #Registration
DROP Table #RegistrationQuestion
[/CODE]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-04 : 04:01:14

do you want all the qn answers for registrant to come as a comma delimited list against each?

Harry C
Posting Yak Master

148 Posts

Posted - 2010-01-04 : 08:32:27

Yes. But I also will likely need to get and HTML output and EXCEL output. But, CSV to start would be nice. Thanks

Harry C
Posting Yak Master

148 Posts

Posted - 2010-01-04 : 17:36:29

Could pivot tables help me here?