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

General SQL Server Forums

New to SQL Server Programming

String Concatenation

Author

Topic

mageshks
Yak Posting Veteran

59 Posts

Posted - 2009-03-25 : 09:27:14

Hi All,
I have a table like the following

col1 col2 col3
---------------------------------------------------
testcol11 testcol12 somestring1

testcol11 testcol12 somestring2

Here the col1 and col2 values in the row 1 and row 2 are same.only col3 value is different

i want the o/p like the following

col1 col2 col3
---------------------------------------------------
testcol11 testcol12 somestring1somestring2

i want the col3 in row1 and row2 to be concatenated

Thanks in advance

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-25 : 09:37:55

Can you try this...

SELECT DISTINCT t.col1,t.col2,(SELECT '' + [col3] FROM @t WHERE col1 = t.col1 FOR XML PATH(''))as [col3]
FROM @t t

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-25 : 12:50:21

and if SQL 2000, you can create UDF for this and use it

something like


CREATE FUNCTION ConcatValues
(
@Col1 varchar(100),
@Col2 varchar(100)
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @RetVar varchar(8000)

SELECT @RetVar=COALESCE(@RetVar,'') + col3
FROM YourTable
WHERE Col1=@Col1
AND Col2=@Col2

RETURN @RetVar
END


and use it like below

SELECT DISTINCT Col1,Col2,dbo.ConcatValues(Col1,Col2)
FROM YourTable

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources