Posted by Plamen Ratchev on 01/08/07 02:53

Here is one way to accomplish what you need using a function. Be aware of
the limitation of 8000 characters for varchar. If you are using SQL Server
2005 you can use varchar(max) to expand beyond the 8000 character limit.

CREATE TABLE Test (
col1 INT,
col2 VARCHAR(10))

INSERT Test (col1, col2) VALUES(1, '1.1')
INSERT Test (col1, col2) VALUES(1, '1.2')
INSERT Test (col1, col2) VALUES(2, '2.1')
INSERT Test (col1, col2) VALUES(2, '2.2')
INSERT Test (col1, col2) VALUES(2, '2.3')

GO

CREATE FUNCTION Concatenate (@col1 INT)
RETURNS VARCHAR(8000)
BEGIN

DECLARE @sReturn VARCHAR(8000)
DECLARE @sDelimiter CHAR(3)

SET @sDelimiter = ' | '

SELECT @sReturn = COALESCE(@sReturn + @sDelimiter, '') + col2 FROM Test
WHERE col1 = @col1 ORDER BY col2

RETURN @sReturn
END

GO

SELECT col1,
dbo.Concatenate(col1) as col2
FROM Test
GROUP BY col1

DROP TABLE Test

DROP FUNCTION Concatenate
GO

Regards,

Plamen Ratchev
http://www.SQLStudio.com


"parth" <Parth.M@gmail.com> wrote in message
news:1168220597.755176.72080@51g2000cwl.googlegroups.com...
> Hi
>
> I want to achieve the following transformation of data using a stored
> procedure.
>
>
> Source
>
> col1 col2(varchar)
> -------------------------
> 1 1.1
> 1 1.2
> 2 2.1
> 2 2.2
> 2 2.3
> =================
>
> Desired Result
>
> col1 col2(varchar)
> --------------------------
> 1 1.1 | 1.2
> 2 2.1 | 2.2 | 2.3
> =====================
>
>
> Thanks in advance. :)
>
> - Parth
>

[Back to original message]