Need to transform single column into a row 1

[RikHess]

I have a need to present a column of data as a single row field.

For example if a query returns a single column with 3 row values:

rec1='AAA'
rec2='BBB'
rec3='CCC'

I want to present it as:

rec='AAA; BBB; CCC'

How can this be done?

Ideally I would like to have this coded in a function.

Thanks for any help.

 

[bborissov]

DECLARE @lcString varchar(8000)
SET @lcString = ''
SELECT @lcString = @lcString + FieldName + ';'

SELECT LEFT(@lcString, LEN(@lcString)-1)

not tested

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[RiverGuy]

Here's an example. Copy and paste this into Management Studio/Query Analyzer and execute. Modify and apply to your data.

DECLARE @T1 TABLE (Column1 VARCHAR(10))
INSERT INTO @T1 SELECT 'AAA'
INSERT INTO @T1 SELECT 'BBB'
INSERT INTO @T1 SELECT 'CCC'

SELECT * FROM @T1

DECLARE @Vals VARCHAR(8000)

SELECT @Vals = ''

SELECT @Vals = @Vals + Column1 + '; '   FROM @T1
IF LEN(@Vals) > 1 SELECT @Vals = SUBSTRING(@Vals, 1, LEN(@Vals) - 1)

SELECT @Vals

 

[RikHess]

RiverGuy and Borislav,

That did the trick!
Thanks.