?
Solved

Get rows into one column with comma seperated values

Posted on 2007-04-11
4
Medium Priority
?
444 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 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

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

801 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