Declare @t table(recordkey int, resultvalue varchar(50))
Declare @s varchar(2000)
Declare @recordkey varchar(10)
set @s=''
Insert into @t values(2671,'Spoke with Carrier Rep')
Insert into @t values(2671,'John Doe')
Insert into @t values(2671,'Passed Timley Filing')
Insert into @t values(2671,'Proof sent')
Insert into @t values(2671,'yes')
Select @recordkey=recordkey from @t
Select @s=@s+' '+ resultvalue from @t
Select @recordkey+''+ @s

USE Northwind
GO
CREATE FUNCTION dbo.ConcatOrderProducts(@OrderID int)
RETURNS VARCHAR(8000)
AS
BEGIN
	DECLARE @Output VARCHAR(8000)
	SELECT @Output = COALESCE(@Output+', ', '') + CONVERT(varchar(20), P.ProductName)
	FROM	dbo.[Order Details] OD
		JOIN dbo.Products P
			ON P.ProductID = OD.ProductID
	WHERE	OD.OrderID = @OrderID
	ORDER BY P.ProductName

	RETURN @Output
END
GO

SELECT OrderID, CustomerID, dbo.ConcatOrderProducts(OrderID)
FROM Orders
GO
DROP FUNCTION dbo.ConcatOrderProducts
GO

quote:

---

If you use front end application, do concatenation there

---

quote:

---

I'd just like to see how it's done once

---

SqlConnection conn = new SqlConnection("data source=(local);initial catalog=Northwind;integrated security=SSPI");
SqlCommand cmd = new SqlCommand("SELECT CustomerID FROM Customers", conn);
string ConcatenatedString = "";
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
	ConcatenatedString += dr.GetString(0); //adjust this to add in separators as required
}
dr.Close();
conn.Close();

quote:

---

Originally posted by Jeff Moden

quote:

---

If you use front end application, do concatenation there

---

Why? It works fine in the database... and do you have a code snippet to show how you would do it in an app? Any language would be fine... After listening to folks say "do it in the app" for years, I've never had anyone show me an example of how to "do it in the app" and I'd just like to see how it's done once (I'm mostly a data troll and I don't very often even get to see the app code)...

--Jeff Moden

---

Set Rs=Con.Execute("Select name from table")
Dim St as string
While not Rs.eof
    St=St&Rs("Name")&","
    Set Rs=Rs.moveNext
Loop

quote:

---

Originally posted by Kristen

I favour the function route too - works nicely with multi-row record sets

USE Northwind
GO
CREATE FUNCTION dbo.ConcatOrderProducts(@OrderID int)
RETURNS VARCHAR(8000)
AS
BEGIN
	DECLARE @Output VARCHAR(8000)
	SELECT @Output = COALESCE(@Output+', ', '') + CONVERT(varchar(20), P.ProductName)
	FROM	dbo.[Order Details] OD
		JOIN dbo.Products P
			ON P.ProductID = OD.ProductID
	WHERE	OD.OrderID = @OrderID
	ORDER BY P.ProductName

	RETURN @Output
END
GO

SELECT OrderID, CustomerID, dbo.ConcatOrderProducts(OrderID)
FROM Orders
GO
DROP FUNCTION dbo.ConcatOrderProducts
GO

Kristen

---

quote:

---

Originally posted by Kristen

"Do you accept the blood of a first born child?"

I'm a consultant. Of course I do

Note that the issue I raised, above, that this may not work under SQL 2005. (in compatibility mode 90)

Kristen

---