Saturday, October 18, 2008

Concatenate Single Column into Row

This query concatenates a single column into one row

CREATE TABLE Numbers
(
number VARCHAR(20)
)
GO
INSERT Numbers SELECT 'one'
INSERT Numbers SELECT 'two'
INSERT Numbers SELECT 'three'
INSERT Numbers SELECT 'four'
GO

declare @list varchar(4000)
set @list = ''
select @list = @list + ',' + number from numbers
select @list = substring( @list, 2, len( @list ) -1) -- To Remove first comma
select @list

OUTPUT
-----------------------------------
one,two,three,four

No comments: