Here is my sample data and I have given the input data set and desired output required.
The data will be always in sequence and the left over values from the next row should append to the main row.

Eg: seq number 4 to 8 and the left over value of 3 (for the column others) and that need to be added with seq number 3

Input Data

seq No  name    adress  Split_indicator Others
-------------------------------------------------
1   |Sample Data | Sample Data | 0 | Other test data 1
2   |Sample Data | Sample Data | 0 | Other test data 2
3   |Sample Data | Sample Data | 0 | Other test data 3
4   |Sample Data | Sample Data | 1 | Other test data 4
5   |Sample Data | Sample Data | 1 | Other test data 5
6   |Sample Data | Sample Data | 1 | Other test data 6
7   |Sample Data | Sample Data | 1 | Other test data 7
8   |Sample Data | Sample Data | 1 | Other test data 8
9   |Sample Data | Sample Data | 0 | Other test data 9
10  |Sample Data | Sample Data | 0 | Other test data 10
11  |Sample Data | Sample Data | 1 | Other test data 11
12  |Sample Data | Sample Data | 1 | Other test data 12
13  |Sample Data | Sample Data | 1 | Other test data 13
14  |Sample Data | Sample Data | 0 | Other test data 14

output format

seq No  name    adress  Split_indicator Others
------------------------------------------------
1   | Sample Data | Sample Data | 0 | Other test data 1
2   | Sample Data | Sample Data | 0 | Other test data 2
3   | Sample Data | Sample Data | 0 | Other test data 3,Other test data 4,Other test data 5,Other test data 6,Other test data 7,Other test data 8
9   | Sample Data | Sample Data | 0 | Other test data 9
10  | Sample Data | Sample Data | 0 | Other test data 10,Other test data 11,Other test data 12,Other test data 13
14  | Sample Data | Sample Data | 0 | Other test data 14

Sample data used

--drop table #temp
create table #temp (seq int, name varchar(100),adress varchar (100), Split_indicator varchar(10),Others varchar(1000))
GO

Insert into #temp values(1,'Sample Data','Sample Data',0,'Other test data 1' )
Insert into #temp values(2,'Sample Data','Sample Data',0,'Other test data 2' )
Insert into #temp values(3,'Sample Data','Sample Data',0,'Other test data 3' )
Insert into #temp values(4,'Sample Data','Sample Data',1,'Other test data 4' )
Insert into #temp values(5,'Sample Data','Sample Data',1,'Other test data 5' )
Insert into #temp values(6,'Sample Data','Sample Data',1,'Other test data 6' )
Insert into #temp values(7,'Sample Data','Sample Data',1,'Other test data 7' )
Insert into #temp values(8,'Sample Data','Sample Data',1,'Other test data 8' )
Insert into #temp values(9,'Sample Data','Sample Data',0,'Other test data 9' )
Insert into #temp values(10,'Sample Data','Sample Data',0,'Other test data 10' )
Insert into #temp values(11,'Sample Data','Sample Data',1,'Other test data 11' )
Insert into #temp values(12,'Sample Data','Sample Data',1,'Other test data 12' )
Insert into #temp values(13,'Sample Data','Sample Data',1,'Other test data 13' )
Insert into #temp values(14,'Sample Data','Sample Data',0,'Other test data 14' )
GO

select * from #temp

1条答案

按热度按时间

wj8zmpe11#

Main issue is probably to identify the group of rows (eg 3 to 8, 10 to 13) for string_agg() . You can use sum() over (order by ) to form the group . As your Split_indicator is actually a string, case expression is used to evaluate it to integer value for sum()

You can execute the inner query to see how the grp works

Query:

select seq = min(seq), name, adress, 
       Split_indicator = min(Split_indicator),
       string_agg(Others, ',')
from
(
  select *, grp = sum(case when Split_indicator = '0' then 1 else 0 end) 
                  over(order by seq)
  from   #temp
) t
group by name, adress, grp

赞(0）回复(0）举报 2023-03-07

SQL Server Get remaining value from next row and append it to the existing row

1条答案

相关问题

热门标签

最新问答