August 13, 2002 at 6:32 am
HI ALL
Lets say I have a "users" table and a "GroupOfUsers" table relaed by Group ID, many users to 1 group. I want to make query that retrieve 1 record for each group including all the group columns + 1 column for all its usernames separated by comma.
This case is not the real case, I facing 5 summilar cases while desgining my db. So I am looking for something like a group by function that combine a string column, I believe there no such thing, So does anyone know how to create one or how to implement the above case in any way?
Thanks!!!
Thanks!!!
August 13, 2002 at 7:17 am
I don't think this is possible either.
The only solution I can see is using a cursor to loop through all the possible groups. You can use the syntax from 'Tame those string part 6' to get the concatenation of all the usernames (in a single statement).
Insert the result in a temporary table, to get the required result.
Don't know if it is not easier to just use a cursor to loop through all the records...
August 13, 2002 at 10:58 pm
Yuck! Are you sure you need to do this?
Ok... You need a function where, say where you pass in a SQL statement and it will return a two column table. Column1 will be your group, column2 your comma delimited list.
Like NPeters said, you'll need a cursor. The sql you pass in will be the basis for your cursor. You'll also need four varchar() control variables and a temp table.
The first fetch from the cursor will set all four variables. After each subsequent fetch, you'll have to compare the new value of column1 with the previous value. If they're different, do your insert into the temp table. If not, tack a comma on to the existing value of the column2 variable and concatonate the new value.
If you end up building this beast, please post the code.
Good luck!
John
August 14, 2002 at 3:56 pm
whohoo!
I acctually tried to do the same thing! I didn't figure it out.
Good luck, you'll need it.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply