How to use Pivot with RowNumber and date

Question

I have a SQL Server table like this:

How can I change reading column into 2 columns based on rownumber?

I have tried like this:

WITH pivot_data AS
(
    SELECT 
        date, CurrentMeterSNID,
        1 + ((ROW_NUMBER() OVER (PARTITION BY CurrentMeterSNID ORDER BY date desc) - 1) % 2)  rownum,
        Reading
    FROM 
        INF_Facility_ElectricalRecord 
)
SELECT 
    date, CurrentMeterSNID, [1], [2]
FROM 
    pivot_data 
PIVOT 
    (MAX(Reading) FOR rownum IN ([1], [2])) AS p;

but the result that I get is:

I get Null record; how can I replace that null value with record from a day after the date?

Answer 1

actually you are not doing PIVOT. You just want to conditionally display the value on different column. For this you use the CASE statement.

For the second requirement : for the NULL value, showing subsequent day value, you can use LEAD() or LAG() window function. This is the else part of the case

select  date, CurrentMeterSNID,
        [1] = case when rownum2 = 1 
                   then reading 
                   else lead(reading) over(partition by CurrentMeterSNID order by date)
                   end,
        [2] = case when rownum2 = 2 
                   then reading 
                   else lead(reading) over(partition by CurrentMeterSNID order by date)
                   end
from    INF_Facility_ElectricalRecord