how do i restructure the results of this query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

Transact-SQL (2000)

how do i restructure the results of this query?

Author

Topic

Zim327
Yak Posting Veteran

62 Posts

Posted - 2009-05-06 : 17:33:42

Hi, I created this query as part of a stored proc and the data all comes out on one line like so:
tracking info attachment info reviewer info
so each tracking info can have multiple attachments and reviewers
I was wondering if there was a way to change it so the attachments are grouped and then the reviewers are grouped.
so that way in my code I just just loop through the recordset and display it like this:
tracking info
-->all attachments
--> -->all reviewers
next tracking record etc etc
Here's the code:


ALTER  PROCEDURE [dbo].[spGetAllTLInfo] 
	@tlid	int = NULL
AS
BEGIN
	SET NOCOUNT ON;
	if (@tlid is null)
	begin
		select tl.*, c.ConID, c.ContractNum, n.*, u1.LName, u1.FName, u2.LName as "RevLN", u2.FName as "RevFN", p.ProgramName, j.ProjName, a.AID, a.attachment,
		rp.* 
		from TrackingList tl left outer join Attachments a on tl.TL_ID = a.TL_ID 
			left outer join Users u1 on tl.UserID = u1.UserID
			left outer join Users u2 on tl.ReviewerID = u2.UserID
			left outer join ReviewersByProject rp on rp.TL_ID = tl.TL_ID and rp.ReviewerID = u2.UserID
			inner join Numbers n on tl.NumID = n.NumID
			inner join Contracts c on c.ConID = n.ConID
			inner join Programs p on c.ProgramID = p.ProgramID 
			left outer join Project j on j.ProjID = c.ProjID and rp.ProjID = j.ProjID;
	end
	else
	Begin
		select tl.*, c.ConID, c.ContractNum, n.*, u1.LName, u1.FName, u2.LName as "RevLN", u2.FName as "RevFN", p.ProgramName, j.ProjName, a.AID, a.attachment, 
		rp.* 
		from TrackingList tl left outer join Attachments a on tl.TL_ID = a.TL_ID 
			left outer join Users u1 on tl.UserID = u1.UserID
			left outer join Users u2 on tl.ReviewerID = u2.UserID
			left outer join ReviewersByProject rp on rp.TL_ID = tl.TL_ID and rp.ReviewerID = u2.UserID
			inner join Numbers n on tl.NumID = n.NumID
			inner join Contracts c on c.ConID = n.ConID
			inner join Programs p on c.ProgramID = p.ProgramID 
			left outer join Project j on j.ProjID = c.ProjID and rp.ProjID = j.ProjID
			where tl.TL_ID = @tlid;		
	end
END

Best regards,
Zim
(Eternal Yak God Emperor from the Future)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-05-06 : 21:02:55

Well, you can get rid of the IF, and just add an ORDER BY:

ALTER  PROCEDURE [dbo].[spGetAllTLInfo] @tlid int = NULL AS
BEGIN
	SET NOCOUNT ON;
	select tl.*, c.ConID, c.ContractNum, n.*, u1.LName, u1.FName, u2.LName as "RevLN", u2.FName as "RevFN", p.ProgramName, j.ProjName, a.AID, a.attachment, 
	rp.* 
	from TrackingList tl left outer join Attachments a on tl.TL_ID = a.TL_ID 
		left outer join Users u1 on tl.UserID = u1.UserID
		left outer join Users u2 on tl.ReviewerID = u2.UserID
		left outer join ReviewersByProject rp on rp.TL_ID = tl.TL_ID and rp.ReviewerID = u2.UserID
		inner join Numbers n on tl.NumID = n.NumID
		inner join Contracts c on c.ConID = n.ConID
		inner join Programs p on c.ProgramID = p.ProgramID 
		left outer join Project j on j.ProjID = c.ProjID and rp.ProjID = j.ProjID
		where @tlid IS NULL OR tl.TL_ID = @tlid
		ORDER BY a.attachment, RevLN, RevFN;
END

Unless I'm missing something (which happens all too often). If this doesn't do it you'll have to post example data to show what you're looking for. This sounds suspiciously like a report or UI thing that's probably better off in that layer instead of in your stored procedure. If so you may also want to post the code that's looping over the recordset.

Zim327
Yak Posting Veteran

62 Posts

Posted - 2009-05-07 : 13:05:48

Thanks for the reply, and I'm sorry that's not quite what I was looking for.
The tracking records have a one to many relationship to attachments and reviewers. Joining all the tables give me all the info on one row. so the attachments and reviewers currently get displayed together.
I need them to be displayed in separate groups as I described above.

I was hoping to do it in one query but I think I'm going to have to break the reviewers out in a separate query and just loop through that.

Thanks again,

Best regards,
Zim
(Eternal Yak God Emperor from the Future)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources