Select grouping fields horizontally

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)

Select grouping fields horizontally

Author

Topic

rekiller
Starting Member

31 Posts

Posted - 2007-09-25 : 18:38:01

I have this registers

ColumA ColumB
0001 AAA
0001 BBB
0001 CCC
0002 DDD
0003 XXX
0004 ZZZ
0004 YYY

I want to select and show the follow:

ColumA ColumnB
0001 AAA,BBB,CCC
0002 DDD
0003 XXX
0004 ZZZ,YYY

Is necessary to use cursor and temp table to achieve this?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-25 : 18:42:09

You can write a function that gives you a concatenated list of columnb's for a given ColumnA.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

rekiller
Starting Member

31 Posts

Posted - 2007-09-25 : 18:49:23

Ok, so, as i said, i need a function with a cursor moving by columnA registers, and grouping them, in a concataned string??
I dit it that, but i though that was an easier solution

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-25 : 18:59:20

quote:
Originally posted by rekiller

Ok, so, as i said, i need a function with a cursor moving by columnA registers, and grouping them, in a concataned string??
I dit it that, but i though that was an easier solution

Maybe this will help:

http://www.4guysfromrolla.com/webtech/092105-1.shtml#postadlink

Future guru in the making.

rekiller
Starting Member

31 Posts

Posted - 2007-09-25 : 19:09:09

Excelent!! That what i wanted!!

ranganath
Posting Yak Master

209 Posts

Posted - 2007-09-26 : 00:30:22

Hi,

Try This Also

Declare @T Table (String varchar(100), Val Varchar(100))
Insert into @T
Select 'A',10 union
Select 'A', 12 union
select 'A',14 union
Select 'B',10 union
Select 'B', 12 union
select 'B',14
select * From @T

Declare @Str1 Varchar(100), @Val Varchar(100)

Update @T
Set @Val = Val = Case when @Str1 = String then @Val + ', ' + Val Else Val End, @Str1 = String

Select String ,Max(Val) From @T Group By String

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 01:49:30

This is a SQL Server 2005 forum, right?

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

E 12°55'05.25"
N 56°04'39.16"

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-26 : 09:01:50

quote:
Originally posted by Peso

This is a SQL Server 2005 forum, right?

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

E 12°55'05.25"
N 56°04'39.16"

Thanks for the information Peter, I only knew of the 2000 way of doing it. I have saved this information in my document store!

Future guru in the making.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources