SQL query - 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

Transact-SQL (2000)

SQL query

Author

Topic

gamolchan
Starting Member

8 Posts

Posted - 2006-10-04 : 11:46:47

I am not sure if this is possible, but here it goes. I have a query that joins 2 tables on an index number and pulls out a customer number, and a customer order. So, if a customer has more then one order, it creates two or more records. Is there a way to have it all in one record.

Instead of this:

Cus# CusOrder
123 book
123 food
111 book

I want this:

Cus# CusOrder
123 book, food
111 book

TIA for any help!

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-10-04 : 12:01:49

Have a look at this: [url]http://www.sqlteam.com/item.asp?ItemID=2368[/url]

Mark

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-04 : 12:13:59

If you use front end application, you can do the concatenation there also

Madhivanan

Failing to plan is Planning to fail

gamolchan
Starting Member

8 Posts

Posted - 2006-10-04 : 16:10:08

Thansk, that helped, but it the script in the article puts all the cusorder info into one record.

book, food, book

I still can;t get it to give me
Cus# CusOrder
123 book, food
111 book

TIA for any help

gamolchan
Starting Member

8 Posts

Posted - 2006-10-04 : 16:26:11

This is what I have so far:

DECLARE @order varchar(1000)
, @cusid varchar(50)
SELECT @order = COALESCE(@order + ', ', '') +
CAST(orderdetail AS varchar(100)), @cusid = cusid
FROM orders
where cusid = 12345

Select @cusid as cusid, @order as order

The output I get is:

CusID Order
12345 Book, Food

I woul like to get the above output for all customer orders. It works as long as i supply the cusid. How can I get it to get every cusid?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-10-05 : 01:45:41

if you can use User Defined function then have a look at this link

http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx

Chirag

gamolchan
Starting Member

8 Posts

Posted - 2006-10-05 : 16:39:58

Thanks, those articles helped solve the issue!!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-05 : 21:14:57

Note that if the concatenated string exceeds 8000 characters, you need to use more than one variable

Madhivanan

Failing to plan is Planning to fail