반응형

/***************************************************************************************
-- 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

 

반응형

+ Recent posts