Group By with an array??? - 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 By with an array???

Author

Topic

poor_leno
Starting Member

2 Posts

Posted - 2007-07-05 : 06:00:52

Hi.

LTRFTP.

Please could someone tell me if it is possible to do a GROUP BY on a table but at the same time put the values of a specific column in to a new field as an array of sorts.

For example;

Original data

ColA / ColB
----------------------
Poor Leno / 1
Poor Leno / 2
Poor Leno / 3
Jez / 1
Mark / 1
Mark / 2
Mark / 3
Mark / 4
Big Sooz / 0
Big Sooz / 1
Big Sooz / 2

Required data

ColA / ColB
----------------------
Poor Leno / 1,2,3
Jez / 1
Mark / 1,2,3,4
Big Sooz / 0,1,2

I hope this makes sense.

If it is possible I'd apprieciate any tips on how I could do this.

Cheers,

Poor_Leno

Free DVD rentals...
[url]http://DVDRentals.TV[/url]

nr
SQLTeam MVY

12543 Posts

Posted - 2007-07-05 : 06:03:41

Easiest to create a function to concatenate the values and use that
see
http://www.nigelrivett.net/SQLTsql/CSVStringFromTableEntries.html

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

poor_leno
Starting Member

2 Posts

Posted - 2007-07-05 : 06:24:59

That's a super whammy beaut.

Thanks nr.

Free DVD rentals...
[url]http://DVDRentals.TV[/url]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-05 : 06:33:21

Since you are using SQL Server 2005, you could try this approach which is faster
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources