[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Get rows into one column with comma seperated values

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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

650 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