Combine row if ... in a querry

Transact-SQL
1

Hello,

I have a view which is similar to this :

ID Rank Flag
1 12 1
2 25 1
3 42 0
4 32 1
5 33 0
6 21 0
7 18 1

And here's the result of the querry I want to do

ID Rank Flag
1 12 1
2 67 1
4 86 1
7 18 1

Basicaly, I have to show rows where Flag = 1 and add the Rank(s) to the row above if Flag=0. I tried with the LAG fonction but can't find a way to manage the multiple Flag=0.

This table has more than 850e6 rows, I can't do it manualy.

To anyone who can help me, thanks.

2

Something like this perhaps:

with cte
  as (select id
            ,row_number() over(order by id) as rn
        from yourtable
       where flag=1
     )
select a.id
      ,sum(rank)
  from cte as a
       left outer join cte as b
                    on b.rn=a.rn+1
       inner join yourtable as c
               on c.id>=a.id
              and c.id<isnull(b.id,999999999)
 group by a.id
;
1 Like
3

Heh... hold the phone a minute. You said that you have to "show rows". Even if the rollup ratio were 850 to 1, that would still be a million rows. If the rollup ratio is only 10 to 1, that means 85e6 rows will be returned. What is the ultimate destination or use of these rows?

4

Hello,

The result will be display in a QlikSense interface, I have over 850e6 rows each month to analyse.

bitsmed, I tried your solution and it's working as expected.

Thanks