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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-11-30 : 09:30:33
|
| xx writes "There is a parent table and a child table. There can be one or more rows in the child table corresponding to a single row in the parent table. There is a character field in the child table (say charField). I want to get a single row from the parent table and a single comma(,)-seperated list of charField values from all the rows of the child table. Would it be possible to do it using a single SQL statement ? Ex : The result set should look like : 'parent1', 'child1,child2,child3''parent2', 'child4,child5''parent3', 'child6''parent4', 'child7,child8,child9,child10'" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2001-11-30 : 11:31:21
|
The thread rob's pointed you to is good. But to answer your question, no, you cannot do it in any generalized way with one SQL statement because it requires an aggregation that is not commutative. (Go on someone, prove me wrong  ) |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2001-11-30 : 11:41:36
|
| Why? |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2001-11-30 : 12:42:58
|
| Why what?Why isn't string concatenation commutative? Kind of fundamental to its operation!Why can't you do non-commutative aggregations? Because all SQL's aggregate functions are commutative.Why can't SQL have non-commutative aggregate functions? Because that would imply an order on the rows being aggregated. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2001-11-30 : 13:06:49
|
I'm just teasing, Arnold.  I meant the question to be more along the lines of "Why should we prove you wrong?" Thanks for the fill-in though, it's good to have more information. |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2001-11-30 : 17:11:38
|
 |
|
|
|
|
|
|
|
|
CSV