Solved

Get rows into one column with comma seperated values

Posted on 2007-04-11
4
441 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
[X]
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
  • 2
  • 2
4 Comments
 
LVL 35

Assisted Solution

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

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 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

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

690 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