Aggregating the results of a column

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

SQL Server 2008 Forums

Transact-SQL (2008)

Aggregating the results of a column

Author

Topic

WindChaser
Posting Yak Master

225 Posts

Posted - 2012-06-06 : 16:27:40

Hi folks,

Don't know if this is possible. On the basis that the following query yields the following results:

Select ID, MyField from MyTable

ID MyField
1 Hello
2 World

I would like to create a Select query which would generate a result like: Hello, World

Is it doable?

Thanks!

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-06-06 : 17:22:08

Try this..

declare @table table
(ID smallint
,MYfield varchar(100)
)

insert @table

select 1,'Hello'
union all
select 2, 'World'

/* QUERY */
SELECT LEFT(l.list,LEN(l.list)-1)
FROM
(
SELECT myfield + ',' AS [text()]
FROM @table
FOR XML PATH(''))l(list)

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-06-06 : 17:23:17

[code]

Solution 1:

create table tab (id int ,Myfield varchar(20))

insert into tab(id,Myfield)
select 1,'Hello'
union all
select 2,'World'

select * from tab

create function fn(@id int )
returns varchar(max) as
begin
declare @col varchar(20)
select @col = coalesce(@col+',','') + Myfield from tab
--print @col
return (@col)
end

select distinct dbo.fn(id) from tab

Solution 2:

select REPLACE(
(select Myfield AS 'data()' from tab for xml path(''))
, ' ', ', ')

Solution 3:

select distinct stuff ((select ',' +t1.Myfield from tab t1
for xml path('')),1,1,'') from tab

[/code]

Vijay is here to learn something from you guys.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources