Multiple rows of data into 1 row results?

Is there a way to get multiple rows of data to show in 1 row of results?
I have a Data Table (ID, Name, Date, etc), Facility Table (ID, Name) and FacilityKey Table (Data ID and Facility ID). The Data table has a 1 to many relationship with the FacilityKey table.

I want to return the Data ID and each Facility Name associated with that Data ID to display in a DataGrid (preferably each Facility Name in the same cell on the grid too), but am stumped on how to get teh query to work (or where to look in the SQL documentation for something like this).

Thanks!What you are trying to do is typically called a pivot table or cross tab query.

Take a look at this post:view post 350666. Someone has posted a suggestion to use a table control to accomplish this. See if anything there helps you. If not, post again :-)

Terri|||I think I may have to do it outside of SQL. I don't want to do any sort of aggregation of the data - just display it in 1 row instead of multiple and I don't see anywhere how to do the SQL cross tab without using an aggregate function of some sort.|||Well, you could write a UDF that would take the DataID as a parameter and would return a comma-delimited list of all of the facilities for that DataID. Like this:

CREATE FUNCTION getFacilities
(@.DataID int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @.facilities VARCHAR(8000)
SET @.facilities = ''
SELECT
@.facilities = @.facilities + ', ' + FacilityTable.FacilityName
FROM
DataTable
INNER JOIN
FacilityKeyTable ON DataTable.DataID = FacilityKeyTable.KeyDataID
INNER JOIN
FacilityTable ON FacilityKeyTable.KeyFacilityID = FacilityTable.FacilityID
WHERE
DataTable.DataID = @.DataID
IF @.facilities <> ''
BEGIN
SET @.facilities = SUBSTRING(@.facilities,3,LEN(@.facilities)-2)
END
RETURN (@.facilities)
END

You would use that function like this:

SELECT DataID, dbo.getFacilities(DataID) FROM DataTable

Terri|||Thank you! That is exactly what I was looking for!|||If efficiency is a priority for you then this function is a poor solution since under the hood it means you are doing N queries for each row returned where N is the number of facilities. A cross-tab query will be more efficient.