Group, then Concatinate - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Group, then Concatinate

Author

Topic

rbh123456789
Starting Member

15 Posts

Posted - 2012-05-31 : 11:18:40

Hey guys, i have looked through many posts, but i haven't really seen an answer to this type of question.

I have a View below, with 2 fields.
The EventID has multiple entries since there are multiple Attendees.

EventID Attendee
13350 John Hart
13350 David Smith
13350 Jane Doe
13353 John Hart
13353 David Smith
13353 Jane Doe
13357 John Hart
13357 David Smith
13357 Jane Doe
13360 John Hart
13360 David Smith
13360 Jane Doe

I was the output to be like below.
I thought I could GROUP BY the EventID, but thats only half of what i need.
Keep in mind that there are actually hundreds of names in the tables.

EventID Attendee
13350 John Hart,David Smith,Jane Doe
13353 John Hart,David Smith,Jane Doe
13357 John Hart,David Smith,Jane Doe
13360 John Hart,David Smith,Jane Doe

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-05-31 : 11:25:12

see
http://www.nigelrivett.net/SQLTsql/CSVStringSQL.html

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-31 : 16:11:53

whats the basis on which you want to order values within comma separated list? do you've another column for this?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-06-14 : 07:44:13

You can do a little variation of a Pivot for this one as follows:


--Creating Table

Create Table Ex
(EventID int,
 Attendee Varchar(30) )


--Inserting Sample Data

Insert Into Ex
Select 13350,	'John Hart'
Union ALL
Select 13350,	'David Smith'
Union ALL
Select 13350,	'Jane Doe'
Union ALL
Select 13353,	'John Hart'
Union ALL
Select 13353,	'David Smith'
Union ALL
Select 13353,	'Jane Doe'
Union ALL
Select 13357,	'John Hart'
Union ALL
Select 13357,	'David Smith'
Union ALL
Select 13357,	'Jane Doe'
Union ALL
Select 13360,	'John Hart'
Union ALL
Select 13360,	'David Smith'
Union ALL
Select 13360,	'Jane Doe'


--Static Pivot

Select EventID, [Name1] + ', ' + [Name2] + ', ' + [Name3] As Attendees From
	(Select *, 'Name' + Cast(ROW_NUMBER() Over (Partition By EventId Order By (Select NULL) ) As Varchar(10) ) As rn From Ex) As a
Pivot
(Max(Attendee) For rn IN ([Name1], [Name2], [Name3]) ) As Pvt


--Dynamic Pivot

Declare @cols varchar(max), @sql varchar(max), @cols1 varchar(max)
Declare @temp Table(Cols varchar(10) )
Insert Into @temp
Select Distinct rn From
	(Select *, 'Name' + Cast(ROW_NUMBER() Over (Partition By EventId Order By (Select NULL) ) As Varchar(10) ) As rn From Ex) As a
Select @cols = Coalesce(@cols + ', ', '') + QUOTENAME(Cols) From @temp
Select @cols1 = Coalesce(@cols1 + '+'', ''+', '') + QUOTENAME(Cols) From @temp
Set @sql = 'Select EventID, '+@cols1+' As Attendees From
				(Select *, ''Name'' + Cast(ROW_NUMBER() Over (Partition By EventId Order By (Select NULL) ) As Varchar(10) ) As rn From Ex) As a
			Pivot
			(Max(Attendee) For rn IN ('+@cols+') ) As Pvt'
Execute (@sql)

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources