SQL Server 에서 mysql 의 group_concat 과 같은 효과 내기
SQL Server2019. 2. 8. 07:20
프로그래밍을 하다보면 특정 칼럼을 기준으로 값들을 모두 합쳐서 보여줘야 하는 경우가 매우 많다. Mysql 의 경우 이런 요청을 일찍 반영하여 group_concat 이라는 문자열 취합 함수를 제공한다.
그런데 왜 mssql 은 이런 함수를 일찍부터 제공하지 않았는지 의문이다. 공식적으로 mssql 2017 버전부터는 string_agg 라는 함수를 포함하여 이 기능을 제공하고 있다.
그렇다면 2017 버전 이하에서는 어떤 방법으로 이 기능을 구현할수 있을까? 다행히 For xml 구문을 이용하여 group_concat 과 같은 효과를 낼수 있다. 다만 쿼리가 길어지고 복잡해진다는 것이 불만이다.
아래에서는 2017 버전 이하를 위한 for xml 쿼리를 이용하는 방법과 2017 버전의 string_agg 함수의 사용방법을 알아보자.
그런데 왜 mssql 은 이런 함수를 일찍부터 제공하지 않았는지 의문이다. 공식적으로 mssql 2017 버전부터는 string_agg 라는 함수를 포함하여 이 기능을 제공하고 있다.
그렇다면 2017 버전 이하에서는 어떤 방법으로 이 기능을 구현할수 있을까? 다행히 For xml 구문을 이용하여 group_concat 과 같은 효과를 낼수 있다. 다만 쿼리가 길어지고 복잡해진다는 것이 불만이다.
아래에서는 2017 버전 이하를 위한 for xml 쿼리를 이용하는 방법과 2017 버전의 string_agg 함수의 사용방법을 알아보자.
SQL Server 2017 버전 이전 (단일테이블에서)
if OBJECT_ID('test_member') is not null
drop table test_member
go
create table test_member(member_id int, member_name varchar(100), depart_name varchar(100))
insert into test_member(member_id, member_name, depart_name) values('1','david','sales')
insert into test_member(member_id, member_name, depart_name) values('2','john','marketing')
insert into test_member(member_id, member_name, depart_name) values('3','harrison','sales')
insert into test_member(member_id, member_name, depart_name) values('4','taylor','marketing')
insert into test_member(member_id, member_name, depart_name) values('5','robert','marketing')
insert into test_member(member_id, member_name, depart_name) values('6','santoshi','sales')
insert into test_member(member_id, member_name, depart_name) values('7','marshall','sales')
SELECT depart_name , members= STUFF(( SELECT ', ' + n.member_name FROM dbo.test_member n WHERE m.depart_name = n.depart_name FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM dbo.test_member m group by depart_name ORDER BY m.depart_name
SQL Server 2017 버전 이전 (멀티테이블에서)
if OBJECT_ID('test_member') is not null
drop table test_member
go
create table test_member(member_id int, member_name varchar(100), birth_year varchar(100))
insert into test_member(member_id, member_name, birth_year) values(1,'david','1977')
insert into test_member(member_id, member_name, birth_year) values(2,'john','1988')
insert into test_member(member_id, member_name, birth_year) values(3,'harrison','1990')
insert into test_member(member_id, member_name, birth_year) values(4,'taylor','1979')
insert into test_member(member_id, member_name, birth_year) values(5,'robert','1980')
insert into test_member(member_id, member_name, birth_year) values(6,'santoshi','1980')
insert into test_member(member_id, member_name, birth_year) values(7,'marshall','1996')
if OBJECT_ID('test_sales_city') is not null
drop table test_sales_city
go
create table test_sales_city(member_id int, city varchar(100))
insert into test_sales_city(member_id, city) values(1, 'new york')
insert into test_sales_city(member_id, city) values(1, 'los angeles')
insert into test_sales_city(member_id, city) values(1, 'orange')
insert into test_sales_city(member_id, city) values(2, 'utah')
insert into test_sales_city(member_id, city) values(3, 'houston')
insert into test_sales_city(member_id, city) values(3, 'tucson')
insert into test_sales_city(member_id, city) values(4, 'adelli')
insert into test_sales_city(member_id, city) values(4, 'new york')
insert into test_sales_city(member_id, city) values(4, 'dc')
insert into test_sales_city(member_id, city) values(5, 'jersey city')
insert into test_sales_city(member_id, city) values(6, 'beach city')
insert into test_sales_city(member_id, city) values(7, 'south beach city')
insert into test_sales_city(member_id, city) values(7, 'siouxfall city')
SELECT m.member_id, m.member_name
, cities= STUFF(( SELECT ', ' + n.city FROM dbo.test_sales_city n WHERE m.member_id = n.member_id FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM dbo.test_member m ORDER BY m.member_id
SQL Server 2017 버전 이후(단일테이블에서)
if OBJECT_ID('test_member') is not null
drop table test_member
go
create table test_member(member_id int, member_name varchar(100), depart_name varchar(100))
insert into test_member(member_id, member_name, depart_name) values('1','david','sales')
insert into test_member(member_id, member_name, depart_name) values('2','john','marketing')
insert into test_member(member_id, member_name, depart_name) values('3','harrison','sales')
insert into test_member(member_id, member_name, depart_name) values('4','taylor','marketing')
insert into test_member(member_id, member_name, depart_name) values('5','robert','marketing')
insert into test_member(member_id, member_name, depart_name) values('6','santoshi','sales')
insert into test_member(member_id, member_name, depart_name) values('7','marshall','sales')
SELECT depart_name, STRING_AGG(member_name, ',') as members
FROM test_member GROUP BY depart_name
SQL Server 2017 버전 이후 (멀티테이블에서)
if OBJECT_ID('test_member') is not null
drop table test_member
go
create table test_member(member_id int, member_name varchar(100), birth_year varchar(100))
insert into test_member(member_id, member_name, birth_year) values(1,'david','1977')
insert into test_member(member_id, member_name, birth_year) values(2,'john','1988')
insert into test_member(member_id, member_name, birth_year) values(3,'harrison','1990')
insert into test_member(member_id, member_name, birth_year) values(4,'taylor','1979')
insert into test_member(member_id, member_name, birth_year) values(5,'robert','1980')
insert into test_member(member_id, member_name, birth_year) values(6,'santoshi','1980')
insert into test_member(member_id, member_name, birth_year) values(7,'marshall','1996')
if OBJECT_ID('test_sales_city') is not null
drop table test_sales_city
go
create table test_sales_city(member_id int, city varchar(100))
insert into test_sales_city(member_id, city) values(1, 'new york')
insert into test_sales_city(member_id, city) values(1, 'los angeles')
insert into test_sales_city(member_id, city) values(1, 'orange')
insert into test_sales_city(member_id, city) values(2, 'utah')
insert into test_sales_city(member_id, city) values(3, 'houston')
insert into test_sales_city(member_id, city) values(3, 'tucson')
insert into test_sales_city(member_id, city) values(4, 'adelli')
insert into test_sales_city(member_id, city) values(4, 'new york')
insert into test_sales_city(member_id, city) values(4, 'dc')
insert into test_sales_city(member_id, city) values(5, 'jersey city')
insert into test_sales_city(member_id, city) values(6, 'beach city')
insert into test_sales_city(member_id, city) values(7, 'south beach city')
insert into test_sales_city(member_id, city) values(7, 'siouxfall city')
select m.member_id, m.member_name, string_agg(c.city) cities
from test_member m inner join test_sales_city c on a.member_id=c.member_id
group by m.member_id, m.member_name
order by m.member_id
'SQL Server' 카테고리의 다른 글
| SQL Server 테이블 데이터 중복제거 방법 (0) | 2019.02.09 |
|---|---|
| SQL Server 조인(join) 을 이용한 데이터 업데이트 방법 (0) | 2019.02.09 |
| SQL Server default 제약조건(constraint) 추가, 변경, 제거 (0) | 2019.02.09 |
| SQL Server 에서 무작위로 (random) 데이터를 가져오는 방법 (0) | 2019.02.09 |
| SQL Server CTE 를 이용한 INSERT, UPDATE, DELETE, 재귀쿼리 예제 (0) | 2019.02.08 |
