Create a string of records from a table in a stored procedure,

[Lauren Quantrell]

I have a table tblCustomers in a one-to-many relationship with table
tblProducts.
What I want to do is to create a stored procudure that returns a list
of each customer in tblCustomers but also creates a field showing a
string (separated by commas)of each matching record in tblProducts.

So the return would look like:
CustID Customer ProductList
1 Smith Apples, Oranges, Pears
2 Jones Pencils, Pens, Paper
etc...

Instead of:

CustID Customer Product
1 Smith Apples
1 Smith Oranges
1 Smith Pears
2 Jones Pencils
2 Jones Pens
2 Jones Paper

Which is what you get with this:

SELECT tblCusomers.CustID, tblCusomers.Customer,
tblProducts.Product
FROM
tblCusomers INNER JOIN
tblProducts ON
tblCustomers.CustID = tblProducts.CustID

I'd appreciate any help!
lq

[Simon Hayes]

"Lauren Quantrell" <la*************@hotmail.com> wrote in message
news:47**************************@posting.google.c om...

I have a table tblCustomers in a one-to-many relationship with table
tblProducts.
What I want to do is to create a stored procudure that returns a list
of each customer in tblCustomers but also creates a field showing a
string (separated by commas)of each matching record in tblProducts.

So the return would look like:
CustID Customer ProductList
1 Smith Apples, Oranges, Pears
2 Jones Pencils, Pens, Paper
etc...

Instead of:

CustID Customer Product
1 Smith Apples
1 Smith Oranges
1 Smith Pears
2 Jones Pencils
2 Jones Pens
2 Jones Paper

Which is what you get with this:

SELECT tblCusomers.CustID, tblCusomers.Customer,
tblProducts.Product
FROM
tblCusomers INNER JOIN
tblProducts ON
tblCustomers.CustID = tblProducts.CustID

I'd appreciate any help!
lq

Generally the best way to do this would be in a front end application, where
it's easier to handle string manipulation. But this thread may be useful if
you have no other choice than to do it in MSSQL:

http://tinyurl.com/bib2

Simon

[Amit Gupta]

la*************@hotmail.com (Lauren Quantrell) wrote in message news:<47**************************@posting.google. com>...

I have a table tblCustomers in a one-to-many relationship with table
tblProducts.
What I want to do is to create a stored procudure that returns a list
of each customer in tblCustomers but also creates a field showing a
string (separated by commas)of each matching record in tblProducts.

So the return would look like:
CustID Customer ProductList
1 Smith Apples, Oranges, Pears
2 Jones Pencils, Pens, Paper
etc...

Instead of:

CustID Customer Product
1 Smith Apples
1 Smith Oranges
1 Smith Pears
2 Jones Pencils
2 Jones Pens
2 Jones Paper

Which is what you get with this:

SELECT tblCusomers.CustID, tblCusomers.Customer,
tblProducts.Product
FROM
tblCusomers INNER JOIN
tblProducts ON
tblCustomers.CustID = tblProducts.CustID

I'd appreciate any help!
lq

You can try the following code in same sequence to get the string of
concatinated records
/*Temp table */
drop table tb_view
Create table dbo.tb_View
(
CustID int,
Customer varchar(20),
Product varchar(20)
)

INSERT INTO tb_View values (1,'Smith','Apples')
INSERT INTO tb_View values (1,'Smith','Oranges')
INSERT INTO tb_View values (1,'Smith','Pears')

INSERT INTO tb_View values (2,'Jones','Pencils')
INSERT INTO tb_View values (2,'Jones','Pens')
INSERT INTO tb_View values (2,'Jones','Paper')

/*Create a function to do the job*/
Create function dbo.fn_concatinate(@CustId as int) returns
varchar(100)
as
begin
declare @ret_value varchar(100)
SET @ret_value=''
Select @ret_value=@ret_value + ',' + Product FROM dbo.tb_View where
CustID=@CustId
RETURN RIGHT(@ret_value,LEN(@ret_value)-1)
end

/*Use function in query */
select CustID,Customer,dbo.fn_concatinate(CustID) from tb_View group
by CustID,Customer

[Erland Sommarskog]

Amit Gupta (am****@hotmail.com) writes:

/*Create a function to do the job*/
Create function dbo.fn_concatinate(@CustId as int) returns
varchar(100)
as
begin
declare @ret_value varchar(100)
SET @ret_value=''
Select @ret_value=@ret_value + ',' + Product FROM dbo.tb_View where
CustID=@CustId
RETURN RIGHT(@ret_value,LEN(@ret_value)-1)
end

Not that this function relies on undefined behaviour. It may return
the expected result, or it may return something else. See
http://support.microsoft.com/default.aspx?scid=287515.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Lauren Quantrell]

Amit,
Thank you for your examples. I realize there is still a lot to learn
for with the SQL. I have never used "Create function" and don't know
where it goes. Sorry for the ignorance...
lq
am****@hotmail.com (Amit Gupta) wrote in message news:<6e**************************@posting.google. com>...

la*************@hotmail.com (Lauren Quantrell) wrote in message news:<47**************************@posting.google. com>...

I have a table tblCustomers in a one-to-many relationship with table
tblProducts.
What I want to do is to create a stored procudure that returns a list
of each customer in tblCustomers but also creates a field showing a
string (separated by commas)of each matching record in tblProducts.

So the return would look like:
CustID Customer ProductList
1 Smith Apples, Oranges, Pears
2 Jones Pencils, Pens, Paper
etc...

Instead of:

CustID Customer Product
1 Smith Apples
1 Smith Oranges
1 Smith Pears
2 Jones Pencils
2 Jones Pens
2 Jones Paper

Which is what you get with this:

SELECT tblCusomers.CustID, tblCusomers.Customer,
tblProducts.Product
FROM
tblCusomers INNER JOIN
tblProducts ON
tblCustomers.CustID = tblProducts.CustID

I'd appreciate any help!
lq

You can try the following code in same sequence to get the string of
concatinated records
/*Temp table */
drop table tb_view
Create table dbo.tb_View
(
CustID int,
Customer varchar(20),
Product varchar(20)
)

INSERT INTO tb_View values (1,'Smith','Apples')
INSERT INTO tb_View values (1,'Smith','Oranges')
INSERT INTO tb_View values (1,'Smith','Pears')

INSERT INTO tb_View values (2,'Jones','Pencils')
INSERT INTO tb_View values (2,'Jones','Pens')
INSERT INTO tb_View values (2,'Jones','Paper')

/*Create a function to do the job*/
Create function dbo.fn_concatinate(@CustId as int) returns
varchar(100)
as
begin
declare @ret_value varchar(100)
SET @ret_value=''
Select @ret_value=@ret_value + ',' + Product FROM dbo.tb_View where
CustID=@CustId
RETURN RIGHT(@ret_value,LEN(@ret_value)-1)
end

/*Use function in query */
select CustID,Customer,dbo.fn_concatinate(CustID) from tb_View group
by CustID,Customer

[Lauren Quantrell]

Specifically...
When I try to cretae a stored procedure containing this Create
Function, I get the error:
"You cannot chnage the object type in a script."
lq

am****@hotmail.com (Amit Gupta) wrote in message news:<6e**************************@posting.google. com>...

la*************@hotmail.com (Lauren Quantrell) wrote in message news:<47**************************@posting.google. com>...

I have a table tblCustomers in a one-to-many relationship with table
tblProducts.
What I want to do is to create a stored procudure that returns a list
of each customer in tblCustomers but also creates a field showing a
string (separated by commas)of each matching record in tblProducts.

So the return would look like:
CustID Customer ProductList
1 Smith Apples, Oranges, Pears
2 Jones Pencils, Pens, Paper
etc...

Instead of:

CustID Customer Product
1 Smith Apples
1 Smith Oranges
1 Smith Pears
2 Jones Pencils
2 Jones Pens
2 Jones Paper

Which is what you get with this:

SELECT tblCusomers.CustID, tblCusomers.Customer,
tblProducts.Product
FROM
tblCusomers INNER JOIN
tblProducts ON
tblCustomers.CustID = tblProducts.CustID

I'd appreciate any help!
lq

You can try the following code in same sequence to get the string of
concatinated records
/*Temp table */
drop table tb_view
Create table dbo.tb_View
(
CustID int,
Customer varchar(20),
Product varchar(20)
)

INSERT INTO tb_View values (1,'Smith','Apples')
INSERT INTO tb_View values (1,'Smith','Oranges')
INSERT INTO tb_View values (1,'Smith','Pears')

INSERT INTO tb_View values (2,'Jones','Pencils')
INSERT INTO tb_View values (2,'Jones','Pens')
INSERT INTO tb_View values (2,'Jones','Paper')

/*Create a function to do the job*/
Create function dbo.fn_concatinate(@CustId as int) returns
varchar(100)
as
begin
declare @ret_value varchar(100)
SET @ret_value=''
Select @ret_value=@ret_value + ',' + Product FROM dbo.tb_View where
CustID=@CustId
RETURN RIGHT(@ret_value,LEN(@ret_value)-1)
end

/*Use function in query */
select CustID,Customer,dbo.fn_concatinate(CustID) from tb_View group
by CustID,Customer

[Lauren Quantrell]

In the DAH! Department...
I realize I have to do the Create Function in Enterprise Manager. I've
been using Microsoft Access MSDE as the front end development tool...
lq

Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn********************@127.0.0.1>...

Amit Gupta (am****@hotmail.com) writes:

/*Create a function to do the job*/
Create function dbo.fn_concatinate(@CustId as int) returns
varchar(100)
as
begin
declare @ret_value varchar(100)
SET @ret_value=''
Select @ret_value=@ret_value + ',' + Product FROM dbo.tb_View where
CustID=@CustId
RETURN RIGHT(@ret_value,LEN(@ret_value)-1)
end

Not that this function relies on undefined behaviour. It may return
the expected result, or it may return something else. See
http://support.microsoft.com/default.aspx?scid=287515.