String concatenation - 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

SQL Server 2000 Forums

SQL Server Development (2000)

String concatenation

Author

Topic

rajesh_yg
Starting Member

2 Posts

Posted - 2005-05-04 : 07:57:52

Is there anyway that I can just use SQL to do string
concatenation on a VARCHAR field much the same way
that a SUM() function is used on a INTEGER field.
I want the output like this

Input Data:
ID Name
-- ----
1 a
1 b
1 c
2 x
2 y

Result:
ID Names
-- -----
1 a, b, c
2 x, y

And MAIN THING IS we need the query without using cursors (low performance), or user defined aggregate functions (available only in Oracle).

PLEASE HELP me in if there is any other efficent way to get the result....

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-05-04 : 07:58:37

These should help you out:

http://www.sqlteam.com/searchresults.asp?SearchTerms=csv

KLang23
Posting Yak Master

115 Posts

Posted - 2005-05-04 : 14:04:37

This isn't pretty, and I would recommend analyzing the execution against using a cursor, but is gives an example of how to concatenate values into a variable given a distinct predicate withoug a cursor. Depeending on the size of the result set, you could consume a siginifncant amount of memory. You can play around the the concatenation to get commas, sizing, etc.

Set NoCount ON
Declare @SQLString Varchar(500)
Declare @Id int
Declare @VTB1 Table (Id int Primary Key, LongName Varchar(500) NULL )
-- Get your unique set of Ids
Insert into @VTB1 (Id) Select Distinct Id From T1 Order By Id

Select @Id = Min(Id) From @VTB1
While @Id is NOT NULL
Begin
Set @SQLString = ''
Select @SQLString = @SQLString + Name From T1 Where Id = @Id
Update @VTB1 Set LongName = @SQLString Where Id = @Id
Set @Id = (Select Top 1 Id From @VTB1 Where Id > @Id)
End
Select * From @VTB1

rajesh_yg
Starting Member

2 Posts

Posted - 2005-05-09 : 07:06:57

I tried function with regular Cursor and also single line query as given below but to retrieve the data its taking 12 SECONDS, WHERE AS ITS TAKING JUST 1 SECOND for the same number of records (3550) when i use for numeric agregate Function SUM. Thats where I got confused.

Fucntion with sigle line function
-----------------------------------

ALTER function dbo.f_GetValSkills1(@vresid integer) returns varchar(1000)
as
begin
DECLARE @EmployeeList varchar(100)
SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') +
CAST(skillkey AS varchar(5))
FROM tblvalskills
WHERE resumekey = @vresid
return @EmployeeList
end

Function with Cursor
--------------------
ALTER function dbo.f_GetReqSkills(@vReqID integer) RETURNS Varchar(4000)
AS
begin
DECLARE @vSkillName varchar(400)
DECLARE @vAllSkillName varchar(4000)
SET @vSkillName = ''
SET @vAllSkillName = ''
DECLARE Line_Item_Cursor CURSOR FOR select tblskills.SkillName from tblreqskills,tblskills where tblreqskills.reqid = @vReqID and tblreqskills.skillkey = tblskills.skillid

OPEN Line_Item_Cursor
FETCH NEXT FROM Line_Item_Cursor INTO @vSkillName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @vAllSkillName = @vAllSkillName + @vSkillName + ', '
FETCH NEXT FROM Line_Item_Cursor INTO @vSkillName
END
CLOSE Line_Item_Cursor
DEALLOCATE Line_Item_Cursor
if len(@vAllSkillName) > 0
begin
set @vAllSkillName = left(@vAllSkillName,len(@vAllSkillName) -1)
end

Return(@vAllSkillName)
end

Thanks & Regards

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources