String Concatenation

Last Post 23 May 2007 03:49 AM by skt5000. 5 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
rosehcm2003
New Member
New Member

--
22 May 2007 01:34 AM
Hello,

I'm have 2 tables

Table1 (CatalogISN, CatalogName)
Table2 (CatalogISN, productISN,productName)

And Example datas

Table1
1 Ring
2 NeckPlace
...

Table2
1 1 Weeding
1 2 Engaged
1 3 Lover
...

I need return string data with 2 columns in SQLserver 2005: 'Ring', 'Weeding, Engaged,Lover'.

Please help.

Thanks
SQLUSA
New Member
New Member

--
22 May 2007 03:33 AM
Here is the perfect example for you: http://www.sqlusa.com/bestpractices2005/sublist/" target="_blank" rel="nofollow">http://www.sqlusa.com/bestpractices...5/sublist/ .

Kalman Toth, Business Intelligence Architect
SQL Server 2005 Training - http://www.sqlusa.com
rosehcm2003
New Member
New Member

--
22 May 2007 06:58 PM
Thanks.
skt5000
New Member
New Member

--
23 May 2007 03:49 AM
This works as well. I don't know if Kalman's solution worked or not as I couldn't test it and I'm not good at reading unfamiliar code and predicting what it will do.

Jim

DECLARE @t1 table (ISN int,CatalogName varchar (20) )
DECLARE @t2 TABLE(ISN int,ProdISN int,ProdName varchar(20))

INSERT INTO @t1 VALUES (1,'Ring')
INSERT INTO @t1 VALUES (2,'Necklace')

INSERT INTO @t2 VALUES(1,1,'Wedding')
INSERT INTO @t2 VALUES(1,2,'Engaged')
INSERT INTO @t2 VALUES(1,3,'Lover')

DECLARE @Comma varchar(1)
DECLARE @str varchar (100)
DECLARE @iMax int
DECLARE @oMax int
DECLARE @iCtr int
DECLARE @OCtr int

SET @Comma = ','
SET @Octr = 1
SET @iCtr = 1

SET @iMax = 3
SET @oMax = 2

WHILE @oCtr <= @oMax

BEGIN
SET @str = ''

WHILE @iCtr <= @iMax
BEGIN
SET @Comma = CASE WHEN @iCtr = 1 THEN '' ELSE ',' END
SET @str = @str + @Comma+(
select b.prodname
from @t1 a
inner join @t2 b
on a.isn = b.isn
where a.ISN = @oCtr
and b.ProdISN = @iCtr
)
SET @iCtr = @iCtr + 1

END
SELECT a.CatalogName,@str
FROM @t1 a
INNER JOIN @t2 b
on a.isn = b.isn
WHERE a.ISN = @oCtr
and b.ProdISN = @iCtr-1

SET @oCtr = @oCtr + 1


END
skt5000
New Member
New Member

--
23 May 2007 04:15 AM
Oops. SET @iCtr = 1 should be right after SET @str = ''
skt5000
New Member
New Member

--
25 May 2007 06:51 AM
I just found these from MWESCH and ScottPletcher from days gone by

-- FROM MWESCH search on 'GROUPING'
create function udf_AppErrors ()
returns @Results table (AppID int, ErrorCodes varchar(50))
as

begin

declare @AppID int,
@ConcatString varchar(50)

set @AppID = -1

while exists (select * from AppError where AppID > @AppID)

begin

set @ConcatString = null

select @AppID = min(AppID) from AppError where AppID > @AppID

select @ConcatString = isnull(@ConcatString, '') + convert(varchar(25), ErrorCode) + ','
from AppError
where AppID = @AppID
order by ErrorCode

if len(@ConcatString) > 0
set @ConcatString = left(@ConcatString, len(@ConcatString) - 1)

insert @Results select @AppID, @ConcatString

end

return

end



-- or from scottpletcher

CREATE FUNCTION dbo.GetErrorCodes (
@appId INT
)
RETURNS VARCHAR(2000)
AS
BEGIN
DECLARE @errorCodes VARCHAR(2000)
SET @errorCodes = ''
SELECT @errorCodes = @errorCodes + CAST(errorCode AS VARCHAR(10)) + ','
FROM application
WHERE appId = @appId
RETURN LEFT(@errorCodes, LEN(@errorCodes) - 1)
END --FUNCTION


SELECT appId, dbo.GetErrorCodes(appId)
FROM (
SELECT DISTINCT appId
FROM application
) AS appIds
You are not authorized to post a reply.

Acceptable Use Policy