One row result - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

One row result

Author

Topic

radoslav
Starting Member

17 Posts

Posted - 2005-10-20 : 13:14:19

Hi
I have the following query:

Select Productname = COALESCE(ProductName + ', ', '')
From Products
Where CategoryID = 6

This will return the result in the following format:

ProductName
--------------------------------
1 Mishi Kobe Niku,
2 Alice Mutton,
3 Thüringer Rostbratwurst,
4 Perth Pasties,
5 Tourtière,
6 Pâté chinois,

I want to see the result in single row, comma separated like this:

ProductName
--------------------------------
1 Mishi Kobe Niku, Alice Mutton, Thüringer Rostbratwurst, Perth Pasties, Seasoning, Tourtière,

How can I combine all rows in one and get this result in sql?
Thanks in advance!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-10-20 : 13:38:17

Is this what you're looking for?

declare @csv varchar(8000)

Select @csv = COALESCE(@csv + ', ' + ProductName, ProductName)
From Products
Where CategoryID = 6

select @csv

Be One with the Optimizer
TG

radoslav
Starting Member

17 Posts

Posted - 2005-10-20 : 14:45:26

Hi TG,
Thank you very much for your help.

My project is little different. I tried to give simple example in my first post.
I would like to combine your select statement with another one like:

---------------------
Select o.OrderID, o.ShipName, p.Productname
From Orders o, Products p
Where OrderID = 10265

declare @csv varchar(8000)
Select @csv = COALESCE(@csv + ', ' + CompanyName, CompanyName)
From Shippers
select @csv AS ShippingMethod

---------------------

Can you help me to combine the two select statements and list ShippingMethod as another column in my first statement?

Thanks!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-10-20 : 15:13:49

One way to do this is to create a function that returns the csv. Here is an example:


use northwind
go
create function dbo.udf_getProductsByOrderID(@orderid int)
returns varchar(8000)
as
begin
	declare @csv varchar(8000)

	select	@csv = coalesce(@csv + ', ' + b.productName, b.productName)
	from	[order Details] a
	join	products b
			on a.productid = b.productid
	where	orderid = @orderid
	order by productName
	
	return @csv
end
go

select	CompanyName, orderDate, dbo.udf_getProductsByOrderID(orderid) as productList
from	customers a
join	orders b
		on a.customerid = b.customerid
where	a.city = 'London'
go
drop function dbo.udf_getProductsByOrderID

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-21 : 01:05:27

Refer this also
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources