concatenate - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

concatenate

Author

Topic

barflyz
Starting Member

47 Posts

Posted - 2010-04-23 : 14:18:54

Hi, I am fairly new to advanced SQL in SQL Server.

I have the following in a table.

ID Products
1 a
1 b
1 c
2 a
2 b
3 r
3 y
3 r

I want to list the ID in a row (1 row per id) followed by a new column which would concatenete the values in the products column.

hopeful result from example above

id products
1 a,b,c
2 a,b
3 a,b,r,y,r

There is an unknown max of products per ID so it has to be dynamic.

If you can post is an descriptive simple manner that would help!
thanks so much

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-23 : 14:24:36

Refer the link..
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-23 : 14:25:48

Using UDF:

CREATE FUNCTION dbo.Concat (@id INT) RETURNS VARCHAR(200)
AS
BEGIN
DECLARE @Concat VARHCAR(200);
SELECT @Concat = ISNULL(@Concat+',', '') + product
FROM table_name
WHERE id = @id
RETURN @Concat
END

SELECT id, dbo.Concat(id)
FROM table_name
GROUP BY id

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 14:32:40

If its SQL 2005, better to go for XML method it performs better than UDF

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

barflyz
Starting Member

47 Posts

Posted - 2010-04-23 : 14:58:29

sorry for these questions but what does UDF stand for?

Also, does (@id INT) I am referring to id from the table? what does (@id INT) mean in business terms? how does it know when to stop looping? thanks

Using UDF:

CREATE FUNCTION dbo.Concat (@id INT) RETURNS VARCHAR(200)
AS
BEGIN
DECLARE @Concat VARHCAR(200);
SELECT @Concat = ISNULL(@Concat+',', '') + product
FROM table_name
WHERE id = @id
RETURN @Concat
END

SELECT id, dbo.Concat(id)
FROM table_name
GROUP BY id

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-23 : 15:17:39

@id is a input parameter that use in body of user-defined function.
First execute CREATE statement then use SELECT statement.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 15:18:57

quote:
Originally posted by barflyz

sorry for these questions but what does UDF stand for?

Also, does (@id INT) I am referring to id from the table? what does (@id INT) mean in business terms? how does it know when to stop looping? thanks

Using UDF:

CREATE FUNCTION dbo.Concat (@id INT) RETURNS VARCHAR(200)
AS
BEGIN
DECLARE @Concat VARHCAR(200);
SELECT @Concat = ISNULL(@Concat+',', '') + product
FROM table_name
WHERE id = @id
RETURN @Concat
END

SELECT id, dbo.Concat(id)
FROM table_name
GROUP BY id

see below to understand different UDF types with examples

http://www.sqlteam.com/article/user-defined-functions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

barflyz
Starting Member

47 Posts

Posted - 2010-04-28 : 09:00:21

How do I pull this function and insert the data into a column into a table?

thanks

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2010-04-28 : 09:08:53

declare @tbl table (id int,part varchar(50))
insert into @tbl values(1,'a')
insert into @tbl values(1,'b')
insert into @tbl values(1,'c')
insert into @tbl values(2,'a')
insert into @tbl values(2,'b')
insert into @tbl values(3,'r')
insert into @tbl values(3,'y')
insert into @tbl values(3,'r')

SELECT t.id,STUFF((SELECT ','+ part FROM @tbl WHERE id=t.id FOR XML PATH('')),1,1,'') AS A
FROM (SELECT DISTINCT id FROM @tbl)t

Karthik
http://karthik4identity.blogspot.com/

barflyz
Starting Member

47 Posts

Posted - 2010-04-28 : 09:37:57

HI, I am trying to use the example above like shown below. I want to concatenate many codes (varchar (200)) for each pid (numeric 19,0) but the code below gives me the error as - Error: Arithmetic overflow error converting varchar to data type numeric. (State:22003, Native Code: 1FB3)

CREATE FUNCTION dbo.Concat (@pid numeric(19,0)) RETURNS numeric(19,0)
AS
BEGIN
DECLARE @Concat varchar(200);
SELECT @Concat = ISNULL(@Concat+',', '') + code
FROM problem
WHERE pid = @pid
RETURN @Concat
END

SELECT pid, dbo.Concat(pid)
FROM problem
GROUP BY pid

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-28 : 09:48:08

quote:
Originally posted by barflyz

HI, I am trying to use the example above like shown below. I want to concatenate many codes (varchar (200)) for each pid (numeric 19,0) but the code below gives me the error as - Error: Arithmetic overflow error converting varchar to data type numeric. (State:22003, Native Code: 1FB3)

CREATE FUNCTION dbo.Concat (@pid numeric(19,0)) RETURNS numeric(19,0)AS
BEGIN
DECLARE @Concat varchar(200);
SELECT @Concat = ISNULL(@Concat+',', '') + code
FROM problem
WHERE pid = @pid
RETURN @Concat
END

SELECT pid, dbo.Concat(pid)
FROM problem
GROUP BY pid

the red line should be:
RETURNS VARCHAR(200)

barflyz
Starting Member

47 Posts

Posted - 2010-04-28 : 09:48:26

I may have figured it out. Will post if not

thanks to all the experts!

you are the best!

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2011-11-08 : 07:16:27

HI I made this concat function:

ALTER FUNCTION [dbo].[ConcatCategories](@ArticleID VARCHAR(10))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Output VARCHAR(8000)
SET @Output = ''

SELECT @Output = CASE @Output
WHEN '' THEN CAST(CategoryID AS VARCHAR)
ELSE @Output + ', ' + CAST(CategoryID AS VARCHAR)
END
FROM ArticleCategories
WHERE ArticleID = @ArticleID
ORDER BY CategoryID

RETURN @Output
END

However, when I get a list of articles, it is kind of a heavy function for SQL server.
So is there a better waY?

The list sp would be something like:

select
articleid,
article,
...,
Categories = dbo.ConcatCategories(ArticleID)
from
articles
where
....

etc.

The secret to creativity is knowing how to hide your sources. (Einstein)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-08 : 07:30:07

quote:
Originally posted by trouble2

HI I made this concat function:

ALTER FUNCTION [dbo].[ConcatCategories](@ArticleID VARCHAR(10))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Output VARCHAR(8000)
SET @Output = ''

SELECT @Output = CASE @Output
WHEN '' THEN CAST(CategoryID AS VARCHAR)
ELSE @Output + ', ' + CAST(CategoryID AS VARCHAR)
END
FROM ArticleCategories
WHERE ArticleID = @ArticleID
ORDER BY CategoryID

RETURN @Output
END

However, when I get a list of articles, it is kind of a heavy function for SQL server.
So is there a better waY?

The list sp would be something like:

select
articleid,
article,
...,
Categories = dbo.ConcatCategories(ArticleID)
from
articles
where
....

etc.

The secret to creativity is knowing how to hide your sources. (Einstein)

please dont reopen old threads
post your question as a new thread

Anyways, fr you if you're using sql 2005 or above use STUFF method given in first link

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources