Join Statement with multiple rows MS SQL Server

[wingnut]

I wish create a view to list results from table a and have all related records(selected fields) from table 2 put in one field within the view. Table 2 will produce multiple rows.

Does this make sense and is it possible?

TIA

[aspdaddy]

This is usually better done in the application that the database, you could loop through the rows and append the values to a string.

But to answerr the question - It probably is possible with a stored procedure, or UDF - I cant think off the top of my head how you could do it using a standard SQL View.

[syber]

Any time that you have a one-to-many relationship in a SQL join, you will get additional rows from the many side with the columns from the one side repeating. So if you don't mind the repeating information (which you can filter out in a report), this could work for you.

[FalseDawn]

I believe that the COALESCE function will allow you to do what you are after:

http: //www.sqlteam.com/item.asp?ItemID=2368

The article is a bit long winded - an example of how to do it is at the bottom, which I'm sure can be tweaked to work in a view.

This article also has examples:
http: //www.aspfaq.com/show.asp?id=2529