?
Solved

Get rows into one column with comma seperated values

Posted on 2007-04-11
4
Medium Priority
?
451 Views
Last Modified: 2010-05-18
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
Comment
Question by:tech1guy
  • 2
  • 2
4 Comments
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 800 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

by:mherchl
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

by:
mherchl earned 1200 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

by:David Todd
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

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

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

Join & Ask a Question