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

**Simon Hayes** · Jul 20 '05, 02:37 AM

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

"Lauren Quantrell" <laurenquantrel l@hotmail.com> wrote in message
news:47e5bd72.0 401190748.491c6 219@posting.goo gle.com...[color=blue]
> 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.Cus tID, tblCusomers.Cus tomer,
> tblProducts.Pro duct
> FROM
> tblCusomers INNER JOIN
> tblProducts ON
> tblCustomers.Cu stID = tblProducts.Cus tID
>
> I'd appreciate any help!
> lq[/color]

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** · Jul 20 '05, 02:37 AM

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

laurenquantrell @hotmail.com (Lauren Quantrell) wrote in message news:<47e5bd72. 0401190748.491c 6219@posting.go ogle.com>...[color=blue]
> 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.Cus tID, tblCusomers.Cus tomer,
> tblProducts.Pro duct
> FROM
> tblCusomers INNER JOIN
> tblProducts ON
> tblCustomers.Cu stID = tblProducts.Cus tID
>
> I'd appreciate any help!
> lq[/color]

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','App les')
INSERT INTO tb_View values (1,'Smith','Ora nges')
INSERT INTO tb_View values (1,'Smith','Pea rs')

INSERT INTO tb_View values (2,'Jones','Pen cils')
INSERT INTO tb_View values (2,'Jones','Pen s')
INSERT INTO tb_View values (2,'Jones','Pap er')

/*Create a function to do the job*/
Create function dbo.fn_concatin ate(@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_valu e,LEN(@ret_valu e)-1)
end

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

**Erland Sommarskog** · Jul 20 '05, 02:37 AM

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

Amit Gupta (amiiit@hotmail .com) writes:[color=blue]
> /*Create a function to do the job*/
> Create function dbo.fn_concatin ate(@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_valu e,LEN(@ret_valu e)-1)
> end[/color]

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, sommar@algonet. se

Books Online for SQL Server SP3 at

Microsoft SQL Server

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

**Lauren Quantrell** · Jul 20 '05, 02:37 AM

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

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

amiiit@hotmail. com (Amit Gupta) wrote in message news:<6e4179ce. 0401200003.67a1 735e@posting.go ogle.com>...[color=blue]
> laurenquantrell @hotmail.com (Lauren Quantrell) wrote in message news:<47e5bd72. 0401190748.491c 6219@posting.go ogle.com>...[color=green]
> > 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.Cus tID, tblCusomers.Cus tomer,
> > tblProducts.Pro duct
> > FROM
> > tblCusomers INNER JOIN
> > tblProducts ON
> > tblCustomers.Cu stID = tblProducts.Cus tID
> >
> > I'd appreciate any help!
> > lq[/color]
>
>
>
> 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','App les')
> INSERT INTO tb_View values (1,'Smith','Ora nges')
> INSERT INTO tb_View values (1,'Smith','Pea rs')
>
> INSERT INTO tb_View values (2,'Jones','Pen cils')
> INSERT INTO tb_View values (2,'Jones','Pen s')
> INSERT INTO tb_View values (2,'Jones','Pap er')
>
> /*Create a function to do the job*/
> Create function dbo.fn_concatin ate(@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_valu e,LEN(@ret_valu e)-1)
> end
>
> /*Use function in query */
> select CustID,Customer ,dbo.fn_concati nate(CustID) from tb_View group
> by CustID,Customer[/color]

**Lauren Quantrell** · Jul 20 '05, 02:37 AM

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

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

amiiit@hotmail. com (Amit Gupta) wrote in message news:<6e4179ce. 0401200003.67a1 735e@posting.go ogle.com>...[color=blue]
> laurenquantrell @hotmail.com (Lauren Quantrell) wrote in message news:<47e5bd72. 0401190748.491c 6219@posting.go ogle.com>...[color=green]
> > 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.Cus tID, tblCusomers.Cus tomer,
> > tblProducts.Pro duct
> > FROM
> > tblCusomers INNER JOIN
> > tblProducts ON
> > tblCustomers.Cu stID = tblProducts.Cus tID
> >
> > I'd appreciate any help!
> > lq[/color]
>
>
>
> 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','App les')
> INSERT INTO tb_View values (1,'Smith','Ora nges')
> INSERT INTO tb_View values (1,'Smith','Pea rs')
>
> INSERT INTO tb_View values (2,'Jones','Pen cils')
> INSERT INTO tb_View values (2,'Jones','Pen s')
> INSERT INTO tb_View values (2,'Jones','Pap er')
>
> /*Create a function to do the job*/
> Create function dbo.fn_concatin ate(@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_valu e,LEN(@ret_valu e)-1)
> end
>
> /*Use function in query */
> select CustID,Customer ,dbo.fn_concati nate(CustID) from tb_View group
> by CustID,Customer[/color]

**Lauren Quantrell** · Jul 20 '05, 02:38 AM

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

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 <sommar@algonet .se> wrote in message news:<Xns947725 BA51CYazorman@1 27.0.0.1>...[color=blue]
> Amit Gupta (amiiit@hotmail .com) writes:[color=green]
> > /*Create a function to do the job*/
> > Create function dbo.fn_concatin ate(@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_valu e,LEN(@ret_valu e)-1)
> > end[/color]
>
> 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.[/color]

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

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

Comment

Comment

Comment

Comment

Comment

Comment