| Author |
 Topic  |
|
|
soulchyld21
Starting Member
United Kingdom
27 Posts |
 Posted - 05/10/2012 : 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)
Singapore
16745 Posts |
|
|
soulchyld21
Starting Member
United Kingdom
27 Posts |
Posted - 05/10/2012 : 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
India
22460 Posts |
Posted - 05/10/2012 : 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
United Kingdom
27 Posts |
Posted - 05/10/2012 : 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
India
47023 Posts |
Posted - 05/10/2012 : 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/
|
|
|
| |
Topic |
|
Multiple results in one row
