Solved

Get rows into one column with comma seperated values

Posted on 2007-04-11
4
434 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 200 total points
Comment Utility
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
Comment Utility
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 300 total points
Comment Utility
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
Comment Utility
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

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…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now