MYSQL GROUP_CONCAT 이후 여러 컬럼으로 나누기

데이터를 묶어서 보고 싶다는 요청을 받았습니다.

먼저 GROUP_CONCAT  함수를 이용하여 쉼표로 연결된 데이터를 제공하였습니다.

반응형

728x90

SELECT A.SEQ, A.NM,  group_concat(B.LT_NM)
FROM TABLE_1 A LEFT OUTER JOIN TABLE_2 B 
ON A.SEQ = B.SEQ AND TYP_CM  IN ('KN','RD')
GROUP BY A.SEQ, A.NM;

1	가시오갈피	
2	가지	가젱이,까재,가재기
3	감	단과
4	감자	마령서,번서,북감저,감재,지슬

 

 

re : 쉼표 대신 개별 컬럼으로 나누어 결과를 받고 싶다는 요청을 받았습니다.

그룹핑한 결과를 다시 여러 컬럼으로 나누어 달라는 요청을 받았습니다.

담당자가 후처리가 귀찮은가 봅니다.

승리의 챗GPT로 검색해보니 동적인 컬럼 제공은 어렵다고 나옵니다. 서비스단에서 가공을 권하고 있습니다.

질문을 잘못 한걸까요. ㅎㅎ 

#chatgpt에 문의한 결과
MySQL에서는 GROUP_CONCAT으로 결합한 컬럼을 다시 여러 개의 컬럼으로 분리하는 것이 쉽지 않습니다. 
이유는 SQL은 동적인 컬럼 생성을 지원하지 않기 때문입니다.
하지만 비슷한 결과를 얻기 위해 일부 프로그래밍 언어나 스크립트에서 다음과 같은 방법을 사용할 수 있습니다. 아래 예제는 PHP를 사용한 것입니다. 
다른 언어에서는 비슷한 방법으로 처리할 수 있을 것입니다.

 

아직 현역인 구글신을 활용해 봅니다.

일단 좀 불편하지만 방법을 찾았습니다. 믿고 있었다고 구글!

구분자로 tab키를 줍니다. 이 결과값을 복사해서 엑셀 시트에 넣으면 각각의 셀에 값이 분리되어 들어갑니다. 

SELECT A.SEQ, A.NM,  group_concat(B.LT_NM) as base,  group_concat(B.LT_NM SEPARATOR '\t') as tip
FROM TABLE_1 A LEFT OUTER JOIN TABLE_2 B 
ON A.SEQ = B.SEQ AND TYP_CM  IN ('KN','RD')
GROUP BY A.SEQ, A.NM;

1	가시오갈피		
2	가지	가젱이,까재,가재기	가젱이	까재	가재기
3	감	단과	단과
4	감자	마령서,번서,북감저,감재,지슬	마령서	번서	북감저	감재	지슬

참고 : https://www.phpschool.com/gnuboard4/bbs/board.php?bo_table=qna_db&wr_id=233278

WWW.PHPSCHOOL.COM

 

 

re : re : 번거롭지 않겠어?

이왕이면 쿼리로만 끝내고 싶습니다. 다행히 그룹핑한 취합컬럼의 항목수가 많지 않습니다. 꼼수가 떠오릅니다.

일회성 데이터 요청에 굳이 기능으로 처리할 일은 아니기에 약간의 수작업을 동반한 쿼리를 만들어서 제공하였습니다.

먼저 그룹핑한 경우 최대 항목의 개수를 구합니다. 이후 해당 개수만큼 분절쿼리를 적용합니다.

이를 잘 활용하면 최대 개수에 맞는 동적인 컬럼을 생성할 수 있습니다. 

SELECT A.SEQ, A.NM,  group_concat(B.LT_NM) AS LT_NMS_ALL, COUNT(B.SEQ) AS CNT,
    IF(COUNT(B.SEQ)>0,SUBSTRING_INDEX(GROUP_CONCAT(B.LT_NM), ',', 1) ,'') AS lt_nms1,
    IF(COUNT(B.REF_SEQ)>1,SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(B.LT_NM), ',', 2), ',', -1) ,'') AS lt_nms2,
    IF(COUNT(B.REF_SEQ)>2,SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(B.LT_NM), ',', 3), ',', -1) ,'') AS lt_nms3,    
    ...
    IF(COUNT(B.REF_SEQ)>9,SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(B.LT_NM), ',', 10), ',', -1) ,'') AS lt_nms10
FROM TBL1 A LEFT OUTER JOIN TBL2 B ON A.SEQ = B.SEQ AND TYP_CM  IN ('KN','RD')
group by A.SEQ, A.NM
ORDER BY CNT DESC;
------------------------------------------------------------
31	귀리	연맥,이맥,작맥,기리,기밀,개밀,끼리,널보리,피보리,괘보리	10	연맥	이맥	작맥	기리	기밀	개밀	끼리	널보리	피보리	괘보리
196	상추	거와,와거,부루,부추,분추,불구,불기,생초,생추	9	거와	와거	부루	부추	분추	불구	불기	생초	생추	
695	가오리	분어,요어,해요어,가우리,가오루,과오리,가부랭이,가브리,가보리	9	분어	요어	해요어	가우리	가오루	과오리	가부랭이	가브리	가보리	
15	고구마	감서,감저,남감저,단감자,저우,고고마,단감재,왜감자	8	감서	감저	남감저	단감자	저우	고고마	단감재	왜감자		
129	목화씨	면실,면자,면화씨,무명씨,목홰씨,목하씨,목해씨,멘네다레	8	면실	면자	면화씨	무명씨	목홰씨	목하씨	목해씨	멘네다레		
217	수수	노제,당서,촉서,촉출,대기지,대끼지,수꾸,쉬시	8	노제	당서	촉서	촉출	대기지	대끼지	수꾸	쉬시		
696	볼락	도부어,두부어,복념어,선정어,열갱이,열기이,이을게이,뽈라구	8	도부어	두부어	복념어	선정어	열갱이	열기이	이을게이	뽈라구		
82	들깨	백소,수임,야임,임자,둘깨,뜰깨,두리깨	7	백소	수임	야임	임자	둘깨	뜰깨	두리깨			
104	마늘	대산,호산,마날,마눌,마눙,마눌종,마늘쫑	7	대산	호산	마날	마눌	마눙	마눌종	마늘쫑			
127	모시풀	라미,래미,모시,저마,모시남,모시낭,모시쿨	7	라미	래미	모시	저마	모시남	모시낭	모시쿨