sql - Dynamically create ranges from numeric sequences -
i have table following:
+----+-----+-----+ | id | grp | nr | +----+-----+-----+ | 1 | 1 | 101 | | 2 | 1 | 102 | | 3 | 1 | 103 | | 4 | 1 | 105 | | 5 | 1-2 | 106 | | 6 | 1-2 | 109 | | 7 | 1-2 | 110 | | 8 | 2 | 201 | | 9 | 2 | 202 | | 10 | 3 | 300 | | 11 | 3 | 350 | | 12 | 3 | 351 | | 13 | 3 | 352 | +----+-----+-----+
i wanted create view groups list grp
, concatenates values in nr
. possible dynamically detect sequences , shorten them ranges? 1, 2, 3, 5
become 1-3, 5
.
so result should this:
+-----+--------------------+ | grp | nrs | +-----+--------------------+ | 1 | 101 - 103, 105 | | 1-2 | 106, 109 - 110 | | 2 | 201 - 202 | | 3 | 300, 350 - 352 | +-----+--------------------+
what got concatenate values, table above become this:
+-----+--------------------+ | grp | nrs | +-----+--------------------+ | 1 | 101, 102, 103, 105 | | 1-2 | 106, 109, 110 | | 2 | 201, 202 | | 3 | 300, 350, 351, 352 | +-----+--------------------+
here's actual statement:
declare @t table ( id int identity(1, 1) , grp varchar(10) , nr int ) insert @t values ('1',101),('1',102),('1',103),('1',105) ,('1-2',106),('1-2',109), ('1-2',110) ,('2',201),('2',202) ,('3',300),('3',350),('3',351),('3',352) select * @t ;with groupnums (rn, grp, nr, nrs) ( select 1, grp, min(nr), cast(min(nr) varchar(max)) @t group grp union select ct.rn + 1, t.grp, t.nr, ct.nrs + ', ' + cast(t.nr varchar(max)) @t t inner join groupnums ct on ct.grp = t.grp t.nr > ct.nr ) select nrs.grp, nrs.nrs groupnums nrs inner join ( select grp, max(rn) mrn groupnums group grp ) r on nrs.rn = r.mrn , nrs.grp = r.grp order nrs.grp
can tell me if it's possible that? great if has idea , share it.
please check try:
declare @t table ( id int identity(1, 1) , grp varchar(10) , nr int ) insert @t values ('1',101),('1',102),('1',103),('1',105) ,('1-2',106),('1-2',109), ('1-2',110) ,('2',201),('2',202) ,('3',300),('3',350),('3',351),('3',352) select * @t ;with t1 ( select grp, nr, row_number() over(order grp, nr) id @t ) ,t ( select *, 1 cnt t1 id=1 union select b.*, (case when t.nr+1=b.nr , t.grp=b.grp t.cnt else t.cnt+1 end) t1 b inner join t on b.id=t.id+1 ) , tn as( select *, min(nr) over(partition grp, cnt) minval, max(nr) over(partition grp, cnt) maxval t ) select grp, stuff( (select distinct ','+(case when minval=maxval cast(minval nvarchar(10)) else cast(minval nvarchar(10))+'-'+cast(maxval nvarchar(10)) end) tn b b.grp=a.grp xml path(''),type).value('.','nvarchar(max)'),1,1,'') [account names] tn group grp
Comments
Post a Comment