How to Aggregate field text values from rows thru view / stored proc?

[kulas]

How to create a SQL view or thru a Stored Procedure...That aggregates field text values from rows?

Example.
---Table Rows--------
GroupID MemberName ' Fields GroupID and Membername
1 Member1
1 Member2
1 Member3
2 Member4

------------------------------------------------
----Desired Output----------------------------
2 fields GroupID and the members that concat the member names.
GroupId Members ---
---------------------------------------------------
1 Member1, Member2, Member3
2 Member4
-----------------------------------------------------


im trying this in sql2000. thx.*

[jp140768]

I know in Access this is called a table pivot, so I looked at books on line, and under pivot, it talks about using analysis services to create a cube.

Perhaps if you investigate the Create Cube statement, it may be of some use to you - to be honest it went a bit over my head.

HTH

[kulas]

i manage to come up with a solution and that is thru functions.

My function returns a table... inside this function i used cursors to get the string values from rows and aggregate them by the groupid.

The function is then used in a view and joined with the Group Table.

i just include the field with the aggreated text values in the select statement.

its my first time to used cursors and i preferred it in function so as to make it locally, am not really sure of any implications , but i hope this is ok.

thanks jp140768 for ur advise. actually i first search for pivoting but i thinks its still more on aggregating numeric values.

[cjard]

jp.. a pivot is slightly different.. unless the OP hasnt explained himself properly (or what I understand of the OP request is flaweed)..

A pivot:
1,a
1,b
1,c
2,a

becomes:
id, col1, col2, col3
1, a, b, c
2, a, null, null


A concatenated transform:
1,a
1,b
1,c
2,a

becomes:
id, col1,
1, "a, b, c"
2, "a"



The OPs use of commas to separate the values makes it look as though he intends N wide columanr grid, but I think he wasnt 2 wide columnar grid where column 2 (col1 in my aliasing) contains a string concat


As for the answer to the OP ; i have none, because I would never do this operation on the server side..

[kulas]

you got it right cjard.

Both on :
1. "concatenated tranform" and
2. "but I think he wasnt 2 wide columnar grid "

I just used the commas to reflect to the user the separation of the text values...("A,B,C,ABC") where ABC is not in anyway related to A or B or C.

and i think ur right this is a bad idea for a server side operation, however on my live site its just an average of 5 multi-user...

And when i tried to process this on the client side, Looks like theres more delay when i load and process em on datasets ( im doing this in Vs2005 BTW / SQL2K).

Thanks a lot for clearing things cjard. hope u can comment on tje solution i did. the function using cursors. thx again.