CODEWITHSUNDEEP
sql-server-2008
FullStack-.NetMVC-BizMule-Dev
FullStack-.NetMVC-BizMule-Dev

Reputation: 803

SQL Server 2008 query to fill the null values

I need a query on below table

TABLE 

ClienName   Bal0    Bal1    Bal2        Bal3        Bal4        Bal5        Bal6    
--------------------------------------------------------------------------------
A           600     600     NULL        NULL        NULL        NULL        NULL    
B           Null    100     NULL        NULL        NULL        NULL        NULL    
C           NULL    NULL    200          NULL       NULL        NULL        NULL        
D           NULL    NULL    NULL        NULL        NULL        130         130     
E           510     510     NULL        510         510         NULL        NULL    
F           170     170     NULL        170         170         NULL        170 
G           210     210     NULL        210         210         210         210     
H           20      20      NULL        20          NULL        NULL        NULL

Result: I would like to see the above TABLE as below, which query can do it? please help 

ClienName   Bal0    Bal1    Bal2        Bal3        Bal4        Bal5        Bal6    
--------------------------------------------------------------------------------
A           600     600     600         600         600         600         600     
B           100     100     100         100         100         100         100     
C           200     200     200         200         200         200         200     
D           130     130     130         130         130         130         130         
E           510     510     510         510         510         510         510     
F           170     170     170         170         170         170         170 
G           210     210     210         210         210         210         210     
H           20      20      20          20          20          20          20

Upvotes: 0

Views: 1021

Answers (2)

rs.
rs.

Reputation: 27427

If you want to only query max (assuming only one value per row) and not update existing table then you can do this

declare @table table(clientname varchar(5), bal0 int, 
                    bal1 int, bal2 int, bal3 int, bal4 int, bal5 int, bal6 int)

insert into @table 
select 'A', 600, 600, null, 600, 600, null, null
UNION ALL
select 'B', null, 120, null, null, 120, null, null

;with cte as (
select clientname, (SELECT MAX(val) 
                    FROM (VALUES (bal0),(bal1),(bal2),(bal3),
                          (bal4),(bal5),(bal6)) AS value(val)) As mCOL
from @table 
)

select t.clientname, mCOL as bal0, mCOL as bal1, mCOL as bal2, mCOL as bal3, 
mCOL as bal4, mCOL as bal5, mCOL as bal6
from @table t inner join cte c on t.clientname = c.clientname

Upvotes: 0

lc.
lc.

Reputation: 116468

Assuming I understand what you want from your very limited example set, just go through the table and fill it column by column.

One pass in the forward direction:

UPDATE theTable
SET Bal1 = Bal0
WHERE Bal1 IS NULL AND Bal0 IS NOT NULL;

UPDATE theTable
SET Bal2 = Bal1
WHERE Bal2 IS NULL AND Bal1 IS NOT NULL;

...

And one pass in the reverse:

UPDATE theTable
SET Bal5 = Bal6
WHERE Bal5 IS NULL AND Bal6 IS NOT NULL;

UPDATE theTable
SET Bal4 = Bal5
WHERE Bal4 IS NULL AND Bal5 IS NOT NULL;

...

This also (assuming "Bal" means balance, and the number has something to do with time) handles the case of differing values in columns as time increases.

If instead you just want the first non-null column, try the COALESCE operator:

SELECT ClienName, COALESCE(Bal0, Bal1, Bal2, Bal3, Bal4, Bal5, Bal6) AS firstBal
FROM theTable

Upvotes: 3

Related Questions