Display Detail Records in Repeated Columns

[MomentSurfer]

Greetings Everyone,

I have a "Project" table, a "UserAccount" table, and a "ProjectResource" table.

The ProjectResource table links a Project to a UserAccount. This is a many-to-many table.

I need to create a VIEW that displays the project information and ALSO displays the first two resources that are assigned (but only the first two). The assigned resources need to appear in columns that are on the same row as the project information.

How would you recommend to approach something like this? Joins would return multiple rows. I need the data consolidated on a single row. Would a sub-select be the approach? I'd rather avoid writing a stored procedure that uses a cursor to create a temp table, if possible.

Here is my sample data structure:

Project
- ProjectID
- ProjectName

UserAccount
- UserAccountID
- UserName

ProjectResource
- ProjectID
- UserAccountID

I want the output to be:

ProjectID, ProjectName, PrimaryResourceName, SecondaryResourceName

I'd appreciate any tips or sample queries you could provide.

Your help is greatly appreciated.

David

 

[vongrunt]

> I need to create a VIEW that displays the project information and ALSO displays the first two resources that are assigned (but only the first two).

First two according to which sort? ORDER BY...

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[MomentSurfer]

Thanks for your response.

I shold have included this in my original post. There is a column in the ProjectResource table called:

ResourceLevel (int)

ResourceLevel = 1 is primary
ResourceLevel = 2 is secondary

Thanks, again, for your response.

David

 

[vongrunt]

There are some 4-5 ways to do that; in this case I would go with vanilla outer joins simply because this code is very extensible (unlike code with sub-selects):

select P.ProjectID, P.ProjectName, UA1.UserName as PrimaryResourceName, UA2.UserName as SecondaryResourceName
from Project P
left outer join ProjectResource PR1 on  P.ProjectID= PR1.ProjectID and PR1.ResourceLevel = 1
	left outer join UserAccount UA1 on PR1.UseraccountID=UA1.UserAccountID 
left outer join ProjectResource PR2 on  P.ProjectID= PR2.ProjectID and PR2.ResourceLevel = 2
	left outer join UserAccount UA2 on PR2.UseraccountID=UA2.UserAccountID 
-- order by whatever

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[MomentSurfer]

Very clean & elegant! I love it.

I really appreciate your help. Thank you.

David