September 20, 2006 at 3:13 am
Hello There
I need some help. I have three tables.
1.Clients (Client information)
2.Application (Application information)
3.Clients_join_Applications. (Acts as a join between the clients table and the application table)
For each application there can be many clients so for each application the Clients_join_Applications table contains an individual record with the client ID and the application ID.
However if I want to get all of the clients for one application the query will return duplicate rows depending on how many clients there are for the application.
Can anyone suggest how I may produce a query that will contain one row per application and will also contain all clients information in the one row. So if there are three clients for one application then the row will contain client_Name1, Client_Name2, Client_Name3.
September 20, 2006 at 5:24 am
hi
if u r using sql server 2005 u can use the PIVOT operator for this.
"Keep Trying"
September 20, 2006 at 5:37 am
I might suggest a T SQL Training course.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
September 21, 2006 at 7:32 am
If this may be more helpfull. I think you'd be better off to do that client side. It's a very basic loop inside another loop.
Can you do this client side? If not why?
September 22, 2006 at 6:22 am
I can do it client side using the application code but I would really like to build an sql statement so that users can also run the query.
September 22, 2006 at 7:01 am
Do you need that in seperate columns or single comma delimited column?
September 22, 2006 at 7:20 am
I would like to view it in separate columns or single comma delimited column. For example in one row I would have Application name, Client 1 name, Client 2 name until all clients have been accounted for.
Which ever method gives me the result would be fine by me.
Am I making sense or are you guys totally lost
September 22, 2006 at 7:25 am
Check the third post from this thread. Just ask if you need help implemanting it :
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=278637
September 22, 2006 at 8:30 am
I have been trying this solution out and it seems to be the answer to my problem.
Thanks very much for your help.
September 22, 2006 at 8:41 am
Great! However if you ever need to have them in separated columns, then just use Chiraq solution and check out the pivot option in the books online.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply