출처1 : http://blog.daum.net/devgroup/31
출처2 : https://stackoverflow.com/questions/1817985/how-do-i-create-a-comma-separated-list-using-a-sql-query
출처3 : https://oracle-base.com/articles/misc/string-aggregation-techniques
-- MySQL
SELECT r.name,
GROUP_CONCAT(a.name SEPARATOR ',')
FROM RESOURCES r
JOIN APPLICATIONSRESOURCES ar ON ar.resource_id = r.id
JOIN APPLICATIONS a ON a.id = ar.app_id
GROUP BY r.name
--SQL Server (2005+)
SELECT r.name,
STUFF((SELECT ','+ a.name
FROM APPLICATIONS a
JOIN APPLICATIONRESOURCES ar ON ar.app_id = a.id
WHERE ar.resource_id = r.id
GROUP BY a.name
FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '')
FROM RESOURCES r
--SQL Server (2017+)
SELECT r.name,
STRING_AGG(a.name, ',')
FROM RESOURCES r
JOIN APPLICATIONSRESOURCES ar ON ar.resource_id = r.id
JOIN APPLICATIONS a ON a.id = ar.app_id
GROUP BY r.name
--Oracle
I recommend reading about string aggregation/concatentation in Oracle.
[로우 데이터]
| ID | Title |
| 1 | 가 |
| 1 | 나 |
| 1 | 다 |
| 2 | 라 |
| 2 | 마 |
| 2 | 바 |
[결과]
| ID | Title |
| 1 | 가, 나, 다 |
| 2 | 라, 마, 바 |
[쿼리]
SELECT DISTINCT
ID,
STUFF((
SELECT ', ' + Title
FROM TB_Data b
WHERE b.ID = a.ID
FOR XML PATH('')
),1,2,'') AS NAME
FROM TB_Data a
