## Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
Solved

# Get rows into one column with comma seperated values

Posted on 2007-04-11
437 Views
Experts,
Basically, I've 2 tables as follows: A customer can have more than one order. Orders table contain multiple rows for each customer.

TABLE: Customers
COLUMNS: CustomerID, CustomerName, CustomerEmail

TABLE: Orders
COLUMN: OrderID, CustomerID

I was trying to achieve the following using the above mentioned 2 tables:

----------------------------------------------------------------------
Customer Name  | Customer Email  | Orders
----------------------------------------------------------------------
Melne           | melne@yh.com   | OR12, OR45
Tony             | tony@yh.com     | OR90, OR85
Mike             | mike@yh.com     | OR19, OR39
----------------------------------------------------------------------

I was not able to achieve the above. Any help will be appreciated.

Thanks
0
Question by:tech1guy
• 2
• 2

LVL 35

Assisted Solution

David Todd earned 200 total points
ID: 18893153
Hi,

Have a look at this:

-- =============================================
-- Comment Block
-- =============================================
--
--
-- =============================================
use pubs
go

if exists
(
select name
from sysobjects
where name = N'test'
and type = 'u'
)
drop table test
go

create table test (
id                  int,
ord                  char( 30 ),
indexnum      int
);

/*
205             apples               125
205             shoes                223
305             pizza                 309
305             marbles             789
*/

set nocount on

insert test values( 205, 'apples', 125 )
insert test values( 205, 'shoes', 223 )
insert test values( 305, 'pizza', 309 )
insert test values( 305, 'marbles', 789 )

set nocount off

-- Values in table
select t2.id, t2.ord, t2.indexnum
from test t2

-- Grouping by id
select t2.id, min( t2.ord )
from test t2
group by t2.id
order by t2.id asc

-- Selecting 1 id
select t2.ord
from test t2
where t2.id = 205

/*
This is the usual way to produce a comma delimited list,
but it does not group it. It returns one string for the
entire results set
*/

declare @OrderList varchar(100)

select
@OrderList = coalesce( @OrderList + ', ', '') + rtrim( t2.ord )
from test t2
where t2.id = 205

select @OrderList

set @OrderList = null
go

-- Using a scaler function to return the comma delimited list
if
object_id (N'dbo.concatFieldNames') is not null
drop function dbo.concatFieldNames
go

create function concatFieldNames (@TableID int)
returns varchar(8000)
as
begin
declare @fields varchar(8000)
set @fields = null
select @fields = coalesce( @fields + ', ', '' ) + rtrim( ord )
from test
where id = @tableid
return @fields
end --function
go

-- The select to use the function
select distinct id, dbo.concatFieldNames(ID)
from test

declare @total int

select
@total = coalesce( @total, 0 ) + indexnum
from test

select @total

select sum( indexnum )
from test

Regards
David
0

LVL 14

Expert Comment

ID: 18893261
try this:

--1. create function:

CREATE function dbo.concat_val (@CustomerID int) returns varchar(1000) as
begin
declare @pom varchar(1000), @x int

set @x = 0

set @pom = ''

select @pom = @pom +  + ', '
from Orders a
where @CustomerID = CustomerID

return case when ltrim(rtrim(@pom)) in ('', ',') then '' else left(@pom, len(@pom) - 1) end

end

--2: select using function:

select CustomerName, CustomerEmail, dbo.concal_val(CustomerID) from Customers
0

LVL 14

Accepted Solution

mherchl earned 300 total points
ID: 18893276
sorry, correct function should be:

CREATE function [dbo].concat_val (@CustomerID int) returns varchar(1000) as
begin
declare @pom varchar(1000), @x int

set @x = 0

set @pom = ''

select @pom = @pom + OrderID + ', '
from Orders a
where @CustomerID = CustomerID

return case when ltrim(rtrim(@pom)) in ('', ',') then '' else left(@pom, len(@pom) - 1) end

end
0

LVL 35

Expert Comment

ID: 18893310
Hi mherchl,

I already wrote this above ...

-- Using a scaler function to return the comma delimited list
if
object_id (N'dbo.concatFieldNames') is not null
drop function dbo.concatFieldNames
go

create function concatFieldNames (@TableID int)
returns varchar(8000)
as
begin
declare @fields varchar(8000)
set @fields = null
select @fields = coalesce( @fields + ', ', '' ) + rtrim( ord )
from test
where id = @tableid
return @fields
end --function
go

-- The select to use the function
select distinct id, dbo.concatFieldNames(ID)
from test

Note that the use of the coalesce function removes the need to rim the last comma.

Although I haven't tested it, My guess is that an empty record set will return null.

Regards
David
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

#### 840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.