1

I need to copy data from rows with non-null rows and if first row is null copy the next non-null value.

it is not a big table. There is no ID column and date column.

The datatype of this column is varchar.

I am using SQL Server 2012

This is how the data is in the table:

Masked
5574-12XX-XXXX-1338
6674-13XX-XXXX-0855
7784-14XX-XXXX-5237
Null
Null
Null
9984-11XX-XXXX-6196
3384-11XX-XXXX-0615
Null
Null

This is expected result.

Masked
5574-12XX-XXXX-1338
6674-13XX-XXXX-0855
7784-14XX-XXXX-5237
7784-14XX-XXXX-5237
7784-14XX-XXXX-5237
7784-14XX-XXXX-5237
9984-11XX-XXXX-6196
3384-11XX-XXXX-0615
3384-11XX-XXXX-0615
3384-11XX-XXXX-0615

online solutions work with simple data. but I am struggling with this data. I am very desperate for the solution. Thanks in advance.

4
  • Hi, welcome to stack. Can you explain more about your question and add your query, table. Dec 16 '20 at 16:16
  • 2
    Please see help me write this query in SQL for how to add details that we need. Dec 16 '20 at 16:33
  • 1
    It would be helpful if you added a minimal reproducible example to your question. Use the edit link at the bottom of the question to add the details to your question.
    – Hannah Vernon
    Dec 16 '20 at 16:39
  • 1
    By definition, rows in a table have no particular order. Your task is impossible if there is no column (or set of columns) that can be used in an order by clause to determine the order you desire. So - does that column (or set) exist?
    – SMor
    Dec 16 '20 at 17:04
0

For that you need an ORDER and another column that helps get the last number

the cte #help1 get you the order with Row# needed for the following tables ChangeIndicator is need t make a group , s that you can get the corect masked value

In #help2 the groupy are established, so that you can get the first vaule of the group, which has the value, you want fr that you need also the order built in #help1.

CREATE TABLE table1
    ([Masked] varchar(19))
;
    
INSERT INTO table1
    ([Masked])
VALUES
    ('5574-12XX-XXXX-1338'),
    ('6674-13XX-XXXX-0855'),
    ('7784-14XX-XXXX-5237'),
    (NULL),
    (NULL),
    (NULL),
    ('9984-11XX-XXXX-6196'),
    ('3384-11XX-XXXX-0615'),
    (NULL),
    (NULL)
;
GO
WITH #help1 as (
select *
,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Row#
, case when [Masked] is null then 0 else 1 end as ChangeIndicator
from [dbo].[table1]
)
, #help2 as (
select *, Sum(ChangeIndicator) over (ORDER BY Row#) RowGroup from #help1
)
select 
case when [Masked] is not null then [Masked]
else first_value([Masked]) over (partition by RowGroup ORDER BY Row#)
end UnitsBalanceFillDown
from #help2
GO
| UnitsBalanceFillDown |
| :------------------- |
| 5574-12XX-XXXX-1338  |
| 6674-13XX-XXXX-0855  |
| 7784-14XX-XXXX-5237  |
| 7784-14XX-XXXX-5237  |
| 7784-14XX-XXXX-5237  |
| 7784-14XX-XXXX-5237  |
| 9984-11XX-XXXX-6196  |
| 3384-11XX-XXXX-0615  |
| 3384-11XX-XXXX-0615  |
| 3384-11XX-XXXX-0615  |

db<>fiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.