AMAZED - but need more help!

[SiFry]

About two years ago I was trying to find a solution to a problem - the problem was trying to do group by queries which allowed me to aggregate text (by concatenation) in just the same way that a SUM aggregation will add numbers in a group. I gave up - it can't be done in SQL92. My final solution was about 500 lines of VB code and a broken spirit. Until this morning - browsing these wonderfull pages I came across the following code

declare @states varchar(255)
select @states = @states + state + ", " from States
print @states -- note, will have an extra ", " that needs to be stripped

It was contributed by Sharon Dooley (Thankyou Sharon)

This function will produce a comma delimited concatenatation of all the states on each row of the states table. Fantastic - there is however a but.

What I need to be able to do is use this functionality at table level so that I can see concatenations of all child data for each parent item - for example a comma delimited list of all authors for each book in the "book - book_authors" relationship.

After one whole day of trying I think I can safely claim this to be impossible (without using a cursor).

Does anyone have any more information on the wonderfull little bit of functionality shown above so that I may continue my struggle - How is it referred to? - Is it actually a documented feature?

Yours

Simon.

[Joyce]

A While Loop functions almost like a cursor but with less of a performance hit.

you many want to try that if you don't want to use a cursor.

What about a Subquery? Have you tried that?

Joyce

------------
SiFry at 8/24/99 12:10:12 PM

About two years ago I was trying to find a solution to a problem - the problem was trying to do group by queries which allowed me to aggregate text (by concatenation) in just the same way that a SUM aggregation will add numbers in a group. I gave up - it can't be done in SQL92. My final solution was about 500 lines of VB code and a broken spirit. Until this morning - browsing these wonderfull pages I came across the following code

declare @states varchar(255)
select @states = @states + state + ", " from States
print @states -- note, will have an extra ", " that needs to be stripped

It was contributed by Sharon Dooley (Thankyou Sharon)

This function will produce a comma delimited concatenatation of all the states on each row of the states table. Fantastic - there is however a but.

What I need to be able to do is use this functionality at table level so that I can see concatenations of all child data for each parent item - for example a comma delimited list of all authors for each book in the "book - book_authors" relationship.

After one whole day of trying I think I can safely claim this to be impossible (without using a cursor).

Does anyone have any more information on the wonderfull little bit of functionality shown above so that I may continue my struggle - How is it referred to? - Is it actually a documented feature?

Yours

Simon.