group_concat 다중 레코드를 한개 컬럼으로 출력.

자꾸 잊어 버려서 기록 합니다.

MySQL에서 Select시 멀티 레코드를 한개 컬럼으로 표현하는 방법입니다.

 한개 컬럼으로 묶어서 쓰는건 concat()함수와 비슷합니다만, concat()함수는  단일 레코드에서 여러개의 컬럼을 묶어서 쓰는 것이고, group_concat()함수는 여러 래코드의 컬럼을 묶어 쓸때 사용합니다.

Sample table : book_mast

book_id	book_name	isbn_no	cate_id	aut_id	pub_id	dt_of_pub	pub_lang	no_page	book_price
BK001	Introduction to Electrodynamics	0000979001	CA001	AUT001	P003	2001-05-08	English	201	85.00
BK002	Understanding of Steel Construction	0000979002	CA002	AUT002	P001	2003-07-15	English	300	105.50
BK003	Guide to Networking	0000979003	CA003	AUT003	P002	2002-09-10	Hindi	510	200.00
BK004	Transfer of Heat and Mass	0000979004	CA002	AUT004	P004	2004-02-16	English	600	250.00
BK005	Conceptual Physics	0000979005	CA001	AUT005	P006	2003-07-16		345	145.00
BK006	Fundamentals of Heat	0000979006	CA001	AUT006	P005	2003-08-10	German	247	112.00
BK007	Advanced 3d Graphics	0000979007	CA003	AUT007	P002	2004-02-16	Hindi	165	56.00
BK008	Human Anatomy	0000979008	CA005	AUT008	P006	2001-05-17	German	88	50.50
BK009	Mental Health Nursing	0000979009	CA005	AUT009	P007	2004-02-10	English	350	145.00
BK010	Fundamentals of Thermodynamics	0000979010	CA002	AUT010	P007	2002-10-14	English	400	225.00
BK011	The Experimental Analysis of Cat	0000979011	CA004	AUT011	P005	2007-06-09	French	225	95.00
BK012	The Nature of World	0000979012	CA004	AUT005	P008	2005-12-20	English	350	88.00
BK013	Environment a Sustainable Future	0000979013	CA004	AUT012	P001	2003-10-27	German	165	100.00
BK014	Concepts in Health	0000979014	CA005	AUT013	P004	2001-08-25		320	180.00
BK015	Anatomy & Physiology	0000979015	CA005	AUT014	P008	2000-10-10	Hindi	225	135.00
BK016	Networks and Telecommunications	00009790_16	CA003	AUT015	P003	2002-01-01	French	95	45.00

Code

SELECT pub_id,GROUP_CONCAT(DISTINCT cate_id
ORDER BY  cate_id ASC SEPARATOR ' ')
FROM book_mast
GROUP BY pub_id ;

Output

group_concat의 재미있는 점은 order by 등을 할수가 있다는 것 :)