데이터베이스 작업을 하다 보면, 종종 여러 행에 있는 데이터를 하나의 문자열로 결합하여 표시해야 할 때가 있습니다. 예를 들어, 동일한 그룹의 모든 항목 이름을 하나의 열에 표시하고 싶을 때가 있습니다. MySQL에서 이 작업을 쉽게 수행할 수 있는 방법은 바로 GROUP_CONCAT 함수를 사용하는 것입니다.

GROUP_CONCAT 함수란?

GROUP_CONCAT 함수는 MySQL에서 제공하는 집계 함수 중 하나로, 그룹화된 데이터에서 여러 값을 하나의 문자열로 결합해 줍니다. 이 함수는 GROUP BY와 함께 사용되며, 여러 행에 걸쳐 있는 값을 하나의 결과로 만들어 줍니다. 결합된 값들은 기본적으로 쉼표(,)로 구분되지만, 다른 구분자를 사용할 수도 있습니다.

기본 사용법

GROUP_CONCAT 함수의 기본 구문은 다음과 같습니다:

GROUP_CONCAT(
  [DISTINCT] column_name 
  [ORDER BY column_name ASC|DESC] 
  [SEPARATOR 'separator_string']
  )
  • DISTINCT: 중복된 값을 제거하고 싶을 때 사용합니다.
  • ORDER BY: 결합할 값을 정렬할 때 사용합니다. 기본적으로 오름차순(ASC)으로 정렬됩니다.
  • SEPARATOR: 결합된 값 사이에 사용할 구분자를 지정합니다. 기본값은 쉼표(,)입니다.

실습 예제: 클래스 이름 결합하기

이제 실제 예제를 통해 GROUP_CONCAT의 사용법을 살펴보겠습니다. 예를 들어, 특정 회사에서 제공하는 워크아웃 클래스의 이름을 하나의 열에 모아서 표시하고 싶다고 가정해 보겠습니다.

SELECT 
  ca.goods_id, 
  GROUP_CONCAT(DISTINCT wc.name ORDER BY wc.name SEPARATOR ', ') AS class_names 
FROM class_authority ca 
LEFT JOIN workout_class wc ON wc.id = ca.class_id 
WHERE wc.company_id = 296 
GROUP BY ca.goods_id;

쿼리 설명

  • ca.goods_id: 클래스와 연결된 상품 ID입니다. 이 필드를 기준으로 그룹화하여 각 상품에 대한 결과를 하나의 행으로 표시합니다.
  • GROUP_CONCAT(DISTINCT wc.name ORDER BY wc.name SEPARATOR ', '): 동일한 goods_id에 속하는 클래스 이름(wc.name)을 중복 제거 후 알파벳 순서대로 정렬하여 쉼표로 구분된 문자열로 결합합니다.
  • LEFT JOIN workout_class wc ON wc.id = ca.class_id: class_authority 테이블과 workout_class 테이블을 조인하여 각 상품 ID에 해당하는 클래스를 가져옵니다.
  • WHERE wc.company_id = 296: 특정 회사(예: company_id = 296)에 속하는 클래스만 선택합니다.
  • GROUP BY ca.goods_id: goods_id를 기준으로 결과를 그룹화하여, 각 상품에 대해 하나의 행만 반환합니다.

결과 예시

위 쿼리를 실행하면 다음과 같은 결과를 얻을 수 있습니다:

101 Yoga, Zumba, Pilates
102 Body Pump, CrossFit, Spin
103 Kickboxing, Muay Thai, Karate

여기서 goods_id = 101에 대한 클래스 이름이 "Yoga, Zumba, Pilates"로 결합되어 표시됩니다. 각 클래스 이름은 알파벳 순서로 정렬되어 쉼표로 구분되었습니다.

결론

GROUP_CONCAT 함수는 데이터를 집계하고 결합하는 데 매우 유용한 도구입니다. 이 함수는 특히 여러 값을 하나의 열에 결합하여 표시해야 할 때 유용하며, 데이터베이스 내에서 간단한 텍스트 조작을 할 수 있게 해줍니다. GROUP_CONCAT을 잘 활용하면 데이터베이스 쿼리를 더욱 강력하게 만들 수 있습니다.

이제 여러분도 GROUP_CONCAT을 사용하여 데이터를 그룹화하고, 필요에 따라 원하는 방식으로 결합해 보세요!

'Database' 카테고리의 다른 글

기존 테이블에 속성 추가  (0) 2021.11.11
동적 피벗 테이블  (0) 2021.01.27
mssql - ISNULL, IN, Procedure  (0) 2021.01.18
mssql - join, between, like '%'  (0) 2021.01.18

글쓴이의 경우 어떤 테이블에 있는 속성을 다른 곳의 테이블에 넣기 위해 해당 명령문으로 테이블의 속성들의 구체적인 값을 확인하였다.

select * from information_schema.columns
where table_name = '테이블 이름'

속성을 확인한 뒤 테이블에 속성을 추가하기 위해 해당 명령문을 사용했다.

alter table 테이블명 add 속성 varchar(6) NULL

 

위 명령문은 원하는 테이블에 varchar(6) 자료형을 가지며 널값을 허용하는 속성을 추가한다.

피벗(PIVOT)의 사전적 의미는 마찰을 적게 하기 위하여 회전하는 축의 지점 끝을 원뿔꼴로 한 것이다. 이 의미를 데이터베이스에서 나름 해석해보자면 테이블을 90도 회전 시켜 열을 행으로 바꿔버리는 그런 느낌의 의미라 할 수 있을 것이다. 

DECLARE @COL AS NVARCHAR(MAX)
DECLARE @QUERY AS NVARCHAR(MAX)


SELECT * FROM SALE 

SELECT DISTINCT
	@COL = STUFF((
		SELECT DISTINCT ',' + QUOTENAME(YYYYMM)
		FROM SALE
		FOR	XML PATH('')
	  ),1,1,'') 
FROM SALE 
SELECT @COL


SET @QUERY = 'SELECT * FROM 
(
SELECT  * FROM SALE
) AS RESULT 
PIVOT( SUM(AMT) FOR YYYYMM IN ('+@COL+'))AS pivot_result'

EXECUTE(@QUERY)

 

우선 아래와 같이 SALE 테이블을 만들어보자

 

SALE TABLE

다 만들었으면 아래와 같이 코드를 만들고 실행하여 보자

SELECT DISTINCT
	@COL = STUFF((
		SELECT DISTINCT ',' + QUOTENAME(YYYYMM)
		FROM SALE
		FOR	XML PATH('')
	  ),1,1,'') 
FROM SALE 
SELECT @COL

STUFF, XML PATH 를 사용하여 YYYMM의 데이터를 한열로 요약하였다.

위 이미지와 같은 결과가 나타나게된다. 위의 결과를 자세히 살펴보면 YYYYMM 속성의 값들이 중복 없이 일열로 나열된 것을 확인할 수 있다. 이를 위해서 STUFF((쿼리문 FOR XML PATH('') ),1,1,'')을 활용하면 된다.  

STUFF에 관해 구체적으로 궁금하다면 아래 사이트를 참조하길 바란다. 

[MSSQL] STUFF와 FOR XML PATH를 활용한 문자열 합치기 (tistory.com)

 

[MSSQL] STUFF와 FOR XML PATH를 활용한 문자열 합치기

MSSQL에서 문자열을 합치는 방법은 여러가지가 있습니다. 이번 포스팅에서는 테이블을 Select 해온 결과값을 문자열로 쉽고 간편하게 만들 수 있는 방법을 소개해 드리도록 하겠습니다. STUFF, FOR XML

coding-factory.tistory.com

그리고 아직 설명하지 않은 QUOTENAME()이 남아있다. QUOTENAME()이 어떤 기능을 가졌는지 이해하기 위해서 우선 QUOTENAME을 없애고 위의 코드를 실행시키면 아래와 같이 [] 대괄호가 사라진 것을 확인할 수 있다. 이 사실로 미루어 보아 QUOTENAME이 대괄호를 만들어주는 역할을 수행하는 것을 알 수 있지만 왜 필요한지 의문이 들 수 있다. 어느 블로그에서 확인한 바로는 피벗 테이블에서 속성으로 만들어주기 위해서는 MSSQL에서는 대괄호로 감싸져 있어야 한다고 한다.

 

이제 아래와 같이 피벗 테이블을 만들어주는 쿼리문을 작성하여 보자. 

SET @QUERY = 'SELECT * FROM 
(
SELECT  * FROM SALE
) AS RESULT 
PIVOT( SUM(AMT) FOR YYYYMM IN ('+@COL+'))AS pivot_result'

EXECUTE(@QUERY)

 피벗 테이블의 일반적인 구조는 아래와 같다. 

SELECT *
FROM (SELECT * FROM 피벗을 원하는 테이블) AS RESULT
PIVOT ( 그룹함수(집계컬럼) FOR 피벗대상컬럼 IN ([피벗컬럼값], ...) AS PIVOT_RESULT

 

 

일반 테이블을 피벗 테이블로 완성시킨 모습

 

PIVOT에 대해 추가적으로 학습하고 싶다면 아래의 블로그를 활용하길 바란다. 

[MSSQL] PIVOT 함수, 행을 열로 바꾸기 (크로스탭, 동적컬럼) (tistory.com)

 

[MSSQL] PIVOT 함수, 행을 열로 바꾸기 (크로스탭, 동적컬럼)

SQL Server에서 행을 열로 변환하기 위해서는 PIVOT() 함수를 이용하면 된다. 특히 그룹함수를 이용하여 통계를 추출할 때 많이 사용된다. SELECT * FROM ( 피벗할 쿼리문 ) AS result  PIVOT ( 그룹합수(집계

gent.tistory.com

 

https://coding-factory.tistory.com/

 

코딩팩토리

My life depends on my efforts

coding-factory.tistory.com

위 사이트 참조

 

ISNULL

- ISNULL은 반환 값이 NULL일 경우 설정된 값을 대신 반환하는 함수이다.

- ISNULL(칼럼, 칼럼이 NULL일 경우 대체할 값)

EX ) ISNULL(WSL.ORDERQTY, 0) - ISNULL(WSL.SHIPQTY, 0) AS NOTSHIPQTY

ISNULL을 사용하면 정의에서도 알 수 있드시 NULL값이 없어지게 된다.

 

IN

SELECT * FROM My_Table WHERE Age IN(20,24,26)

SELECT * FROM My_Table WHERE Age = 20 OR Age = 24 OR Age = 26

반대로 어떤 값에 해당사항이 없는 값을 불러오고 싶을 때는 NOT IN을 사용하면 된다.

 

참조하면 좋은 사이트

https://greatepig.tistory.com/19

 

[DB]프로시저 vs 사용자 정의 함수

<서론> 프로시저에 비즈니스 로직이 녹아있는 프로젝트를 하다보니 여러 프로시저 안에서 다른 프로시저를 부르는 경우를 많이 봤다. 그런데 간혹 프로시저 말고, 사용자 정의 함수를 호출하는

greatepig.tistory.com

PROCEDURE

프로지셔란 프로그래머가 생성해놓은 쿼리문을 마치 하나의 메서드 형식으로 관리된다.

 

기본적인 프로시져 형태 

CREATE PROCEDURE [프로시져명]
(
	@변수명 변수타입,
	@변수명 변수타입,
)
AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

쿼리문

END;

변수명 앞에는 @을 사용해야 한다.

 

특징(사용자 정의 함수와 반대되는)

- 트랜잭션을 사용할 수 있다.

- SELECT, HAVING, WHERE 위에서 사용할 수 없다.

 

그 외 프로시져에서 자주 등장하는 구문

- SET NOCOUNT ON

  MSSQL에서 INSERT, UPDATE, 등을 사용하게 되면 영향받은 쿼리의 개수를 세어주는 기능이 있는데 이 기능은 딱히 의미 없는 기능이기 때문에 성능이 중요한 프로시져의 경우 SET NOCOUNT ON을 사용하여서 세어주는 기능을 사용하지 않을 수 있다.

 

- SET TRANSACTION ISOLATION LEVEL READ UNCOMMITEED

ISOLATION LEVEL은 격리 수준으로 현재 READ UNCOMMITED을 사용하고 있다.

격리 수준은 4가지가 있고 아래로 갈수록 격리 수준이 강해진다.

 

- READ UNCOMMITTED

- READ COMMITTED

- REPEATABLE READ

- SERIALIZABLE

 

여기서 READ UNCOMMITTED 같은 경우에는 트랜잭션이 실행은 되었지만 커밋되지 않은 데이터도 읽겠다는 뜻이다.

이렇게 될 경우 정합성의 문제가 발생하거나 더티 리드 현상이 발생하게 될 수도 있다.

 

 

 

 

 

 

 

           SELECT
             WMS.COMPANYID        /*회사코드*/
            ,WMS.DIVISIONID       /*사업장코드*/
            ,WMS.SOID             /*수주번호*/
            ,WMS.CUSTOMERID       /*거래처코드*/
            ,WMC.CUSTOMERNAME     /*거래처명*/
            ,WMS.ORDERDATE        /*수주일자*/
            ,WMS.DELIVERYDATE     /*납기일자*/
            ,WML.PRICE         /*수주금액*/
            ,WML.SOLINENO         /*행번*/
            ,WML.ITEMID           /*아이템코드*/
            ,WMI.ITEMNAME         /*아이템명*/
            ,WML.ORDERQTY         /*수주수량*/
            ,WML.SHIPQTY          /*출고수량*/
            ,(WML.ORDERQTY - WML.SHIPQTY) AS NOTSHIPQTY /*미출고수량*/
            ,WML.SHIPSTATE        /*출고상태*/
            ,WMS.REMARK           /*설명*/
            ,WMS.ISVALID
            FROM      
			WM_SALESORDER     WMS
            INNER JOIN WM_SALESORDERLINE WML        ON WMS.SOID       = WML.SOID        AND WML.ISVALID = 'Y'
            LEFT  JOIN WM_CUSTOMERINFO   WMC        ON WMS.CUSTOMERID = WMC.CUSTOMERID  AND WMS.COMPANYID = WMC.COMPANYID  AND WMS.DIVISIONID = WMC.DIVISIONID
            LEFT  JOIN WM_ITEMINFO       WMI        ON WML.ITEMID     = WMI.ITEMID
            WHERE
            WMS.COMPANYID = 1000
            AND WMS.DIVISIONID = 1000
			AND WMS.ORDERDATE BETWEEN '2021-01-01' AND '2021-01-30'
			AND WMS.SOID LIKE '%SO%'

위 쿼리문에서 주의 깊게 봐야될 조건은 BETWEEN 조건 AND 조건 과, LIKE '%' 이다.

BETWEEN은 어떤 값보다는 작고 어떤 값보다는 큰 조건을 만들 때 편리하게 사용할 수 있다.

어떤 단어를 검색할 때 특정 키워드만 사용하고 싶으면 %를 활용하면 된다. 

'-' : 글자숫자를 정해줌(EX 컬럼명 LIKE '홍_동')

'%' : 글자숫자를 정해주지않음(EX 컬럼명 LIKE '홍%')

SELECT * FROM WM_SALESORDER WHERE SOID = 'SO2011120002'

BEGIN TRAN
DELETE FROM WM_SALESORDER WHERE SOID = 'SO2011120002'
ROLLBACK TRAN

SELECT * FROM WM_SALESORDER WHERE SOID = 'SO2011120002'

위 쿼리문은 트랜잭션을 실험해보기 위함이다. 

BEGIN TRAN이 실행된 뒤 다음에 실행되는 쿼리문을 ROLLBACK TRAN을 이용하여 되돌린 후 다시 SELECT문을

사용하여 DELETE되기 전으로 되돌아 갔는지 확인하였고 결과는 ROLLBACK이 성공적으로 이루어 졌다.

 

INNER JOIN LEFT OR RIGHT OUTER JOIN

https://m.blog.naver.com/PostView.nhn?blogId=wideeyed&logNo=221435077767&proxyReferer=https:%2F%2Fwww.google.com%2F

 

[MSSQL] 조인(Inner Join, Outer Join)

조인(Join)이란 2개 이상 테이블을 서로 엮어 조회하는 것이다.​Inner Join은 서로 매칭되는 것만 엮어...

blog.naver.com

 

교집합(JOIN, INNER JOIN)
합집합

 

LEFT OUTER JOIN 과 LEFT JOIN은 같고 위에 WHERE 절이 빠지게 되면 교집합 부분도 포함하게 된다.

+ Recent posts