how to copy Previous Cell value when there is null value

_{Thursday, July 17, 2014 11:08 AM}

Hello

Following is my data.

ID     Name
1      A
2      NULL  // Previous Row Name = A should be copy here using some query
3      B
4      C
5      NULL  // Previous Row Name = C should be copy here using some query
6      D

I want to do something like when there is Null value in Name Column then it should copy previous cell's value.

EG:

In here scenario ID 2 has Name NULL & ID 1 has Name A. So I want a query which will copy ID 1's Name value to ID 2's Name column 

Can any one guide me to do this kind of query please?

Thanking you in advance..

Regards

Vipul Langalia

_{Thursday, July 17, 2014 11:23 AM ✅Answered | 1 vote}

If its sql 2012 use this

SELECT ID,COALESCE(Name,LAG(Name,1) OVER (ORDER BY ID )) AS NameFROM Table

If there can be multiple gaps use this instead

SELECT ID,COALESCE(Name,MAX(COALESCE(Name,'')) OVER (ORDER BY ID  ROWS BETWEEN UNBOUNDED PRECEDING AND  1 PRECEDING)) AS NameFROM Table

Please Mark This As Answer if it helps to solve the issue Visakh http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

_{Thursday, July 17, 2014 11:23 AM ✅Answered}

CREATE TABLE #C (X CHAR(1) , ID INT)
INSERT INTO #C values('A', 1)
INSERT INTO #C values(NULL, 2)
INSERT INTO #C values('B', 3)
INSERT INTO #C values('C', 4)
INSERT INTO #C values(NULL, 5)
INSERT INTO #C values('D', 6)

SELECT ID,CASE WHEN X is not null
            THEN X
            ELSE (SELECT max(X)
                  FROM #C
                  WHERE ID <= t.ID)
       END AS X
FROM #C t

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

_{Thursday, July 17, 2014 11:25 AM ✅Answered}

If its earlier version use this

SELECT t.ID,COALESCE(t.Name,t1.Name) AS NameFROM table tOUTER APPLY (SELECT TOP 1 NameFROM tableWHERE ID < t.IDAND Name IS NOT NULLORDER BY ID DESC)t1

Please Mark This As Answer if it helps to solve the issue Visakh http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs