반응형
/***************************************************************************************
-- Title : [SQL2017] STRING_AGG() 함수를 이용한 멀티값을 싱글값으로 변환
-- Reference : doc.microsoft.com, sqlperformance.com
-- Key word : 멀티값 싱글값 멀티로우 싱글로우 멀티 로우 싱글 로우 string_agg string_agg() within group 멀티행 싱글행 within group
***************************************************************************************/
■ Scripts
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
|
use tempdb;
go
/*
-- Drop table
*/
drop table ttt;
/*
-- Create table
*/
create table ttt
( id int
, sect nvarchar(10)
, aa nvarchar(10)
, bb nvarchar(max)
);
/*
-- Insert data
replicate는 8000자까지 암시적으로 잘라버리기에 max길이로 넣을려면 명시적으로 지정해야 함.
*/
insert into ttt values
(1, 'AA', 'a11111', replicate(cast('b1' as nvarchar(max)), 100000)),
(2, 'AA', 'a22222', replicate(cast('b2' as nvarchar(max)), 100000)),
(3, 'AA', 'a33333', replicate(cast('b3' as nvarchar(max)), 100000)),
(4, 'BB', 'a44444', NULL),
(5, 'BB', NULL, replicate(cast('b5' as nvarchar(max)), 100000)),
(6, 'BB', 'a66666', replicate(cast('b6' as nvarchar(max)), 100000)),
(7, 'CC', 'a77777', replicate(cast('b6' as nvarchar(max)), 100000)),
(8, 'DD', 'a88888', NULL),
(9, 'DD', 'a99999', NULL);
/*
-- select table
*/
select *, len(bb) "len_bb"
from ttt;
/*
-- Query w/ STRING_AGG()
NULL값은 무시됨. 건수를 맞추기 위해서는 필요시 ISNULL(??, '') 이딴식으로 꼭 하라는 공구라의 말씀.
*/
select string_agg(aa, ';') "agg_aa", len(string_agg(aa, ';')) "len_agg_aa"
, string_agg(bb, ';') "agg_bb", len(string_agg(bb, ';')) "len_agg_bb"
from ttt;
select sect
, string_agg(aa, ';') "agg_aa", len(string_agg(aa, ';')) "len_agg_aa"
, string_agg(bb, ';') "agg_bb", len(string_agg(bb, ';')) "len_agg_bb"
from ttt
group by sect;
/*
-- Query w/ STRING_AGG() & WITHIN_GROUP
NULL값은 암시적으로 ISNULL() 처리 됨.
*/
drop table #Ttt
CREATE TABLE #ttt (
[State] VARCHAR(5),
[Cities] VARCHAR(50)
);
INSERT INTO #ttt
SELECT 'CA', 'Hanford' UNION ALL SELECT 'CA', 'Fremont' UNION ALL SELECT 'CA', 'Los Anggeles'
UNION ALL SELECT 'CO', 'Denver' UNION ALL SELECT 'CO', 'Aspen' UNION ALL SELECT 'CO', 'Vail'
UNION ALL SELECT 'CO', 'Teluride' UNION ALL SELECT 'WA', 'Seattle' UNION ALL SELECT 'WA', 'Redmond'
UNION ALL SELECT 'WA', 'Bellvue' UNION ALL SELECT 'CA', NULL;
SELECT * FROM #ttt;
-- 저장된 정렬 순으로 Aggregation(비보장)
select state, string_agg(cities, ';') "cities"
from #ttt
group by state;
-- WITHIN_GROUP으로 정렬 보장
select state, string_agg(cities, ';') WITHIN GROUP (ORDER BY cities) "cities"
, count(*) "cnt", count(cities) "cnt_cities"
from #ttt
group by state;
select state, string_agg(cities, ';') WITHIN GROUP (ORDER BY cities DESC) "cities"
, count(*) "cnt", count(cities) "cnt_cities"
from #ttt
group by state;
|
cs |
■ Compare Performance
c.f. sqlperformance.com
■ String 병합 시 정렬 적용
WITHIN GROUP~ ORDER BY 활용하여 정렬 순서 적용 가능
select sect, string_agg(aa, '|') within group(order by aa) "string_agg"
from ttt
group by sect
반응형