Hi all,

I have two M:M tables, A and B, which are connected by a 3rd link table C.
Each row of A might be connected to 0 to 10 row(s) of B.

A (ID, AName)
C (A.ID, B.ID)
B (ID, Bname)

And when I query AName = ‘Smith’, normally, it returns:

Smith | Hat
Smith | Coat
Smith | Shoes

My question:
Is there a way to DISPLAY this result in ONE row without changing the tables’ structure?
Like -> Smith | Hat | Coat | Shoes

Any comment and help would be appreciated.

Thanks
Sam

Yes, but not using pure SQL. You will have to write application code to query the data order by ANAME and then keep concatenating the BNAME values together until the ANAME changes.

there sure is, but only in mysql version 4.1+, and sybase ase

... or Oracle 9i like this.

WTF OMG LOL

that's horrible!!

True, but at least Tom Kyte has done all the work for us. All we have to do is install his STRAGG function once, make it public, and from then on just use it, i.e.:

select deptno, stragg(ename)
from emp
group by deptno;

(But it would be neat if it came built-in to the DBMS I agree).

Thanks a lot Tony and Rudy.

I thought there might be a SQL trick (!) to do that and I would put myself in disadvantage if I didn't ask you about that.

I will try this again when I start my application development. Any reference, link, or hint on this subject would be helpful for me. I am not even sure what such a process is called, "multi-column to one-row"?

Rudy, I was wondering if you could address the section of MySQL manual for doing that job in MySQL 4.1+ as you mentioned. (I am going to use MySQL 4.1.x and PHP)

Thanks again
Sam

it's called "denormalization"

in mysql it's the GROUP_CONCAT function (see 12.9.1 GROUP BY (Aggregate) Functions)

using your tables A,B,C above, you would write:

Yes! That was what I was looking for.

Thank you very much Rudy. You solved my problem again. Your solution works very well. Especially, with that separator '|' you recommended, the result is very readable.

You are not only very knowledgeable but also an awesome teacher. I must enroll in your "online course SQL for Database-Driven Web Sites". I am sure I will learn a lot from you.

Regards,
Sam