Multiple results in one row - 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

General SQL Server Forums

New to SQL Server Programming

Multiple results in one row

Author

Topic

soulchyld21
Starting Member

28 Posts

Posted - 2012-05-10 : 08:36:05

Hi, I have a table with lots of records for a specific customer eg

custnum orderid
205443 384899
205443 372828
205443 361467
205443 372828

I would like to structure a query that will retrieve the results for customer 205443 in such a way that all the different order numbers appear in the same cell or even different cells but in the same row, Is this possible?
This would save me from displaying the customer number multiple times as show above making the data easier to read

a visual representation of what Im trying to achieve might look something like this

custnum orderid
205443 384899,372828,361467,372828

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-10 : 08:40:05

see http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

KH
[spoiler]Time is always against us[/spoiler]

soulchyld21
Starting Member

28 Posts

Posted - 2012-05-10 : 09:01:41

Thanks, How then would I select all the records pertaining to a custnum and union those records? bearing in mind I may have thousands of customers all with multiple records!

Many thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-05-10 : 09:09:51

You just use the select statement alone and use your table name

Madhivanan

Failing to plan is Planning to fail

soulchyld21
Starting Member

28 Posts

Posted - 2012-05-10 : 09:22:42

SELECT distinct custnum, STUFF((SELECT ',' + orderid FROM MyTable FOR XML PATH('')), 1, 1, '') AS 'Products Purchased'

FROM MyTable
order by custnum

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-10 : 12:00:38

quote:
Originally posted by soulchyld21

SELECT distinct custnum, STUFF((SELECT ',' + CAST(orderid AS varchar(10)) FROM MyTable FOR XML PATH('')), 1, 1, '') AS 'Products Purchased'

FROM MyTable
order by custnum

Add this small modification if orderid is of numeric type

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources