Reporting Services - get previous column value if current is NULL

Question

I am trying to reduce the size of the data a T-SQL query returns to the Reporting Services. For example, let's say we have the following row set:

ID  Country City
1   Germany Berlin
2   Germany Berlin
3   Germany Berlin
4   Germany Berlin
5   Germany Hamburg
6   Germany Hamburg
7   Germany Hamburg
8   Germany Hamburg
9   Germany Berlin
10  Germany Berlin

It can be transform easily to this:

ID  Country City
1   Germany Berlin
2   NULL    NULL
3   NULL    NULL
4   NULL    NULL
5   NULL    Hamburg
6   NULL    NULL
7   NULL    NULL
8   NULL    NULL
9   NULL    Berlin
10  NULL    NULL

As I may have thousands of thousands of duplicated values (and hundreds of columns), I know that transforming the data using NULLs like this reduce dramatically the size of the returned data.

Is it possible, to implement a formula, which get's the previous row column value, if the current one is NULL?

I want to test if it will be faster to just render huge data or to work with smaller data but apply such expression.

Answer 1

Why not just use Group BY

SELECT Min(Id) as min, Max(ID) as max,Country,City 
FROM myTable 
GROUP BY Country,City

Or

SELECT count(Id) as rowRepeatNum,Country,City 
FROM myTable 
GROUP BY Country,City

The first approach would work if your Ids were sequential for this although I'm not sure if the Id is important.

The second way can give you an number to loop down to generate repeat rows in your application fairly quickly and return significantly less rows.

Can give me more info on your use case?