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

Reputation: 803

sql server 2008, altering the table layout

Please find the blow table.

TABLE 

ClientID    Balance1    Balance2    Balance3    Balance4    Balance5    Balance6    Balance7    Balance8    Balance9
A           NULL        NULL        NULL        3           Null        Null        Null        Null        Null
B           10          null        Null        Null        20          Null        Null        Null        NULL
C           Null        8           Null        10          Null        Null        1           Null        NULL
D           Null        19          Null        Null        Null        Null        Null        Null        NULL
E           Null        Null        50          Null        Null        Null        Null        Null        NULL
F           NULL        NULL        NULL        30          NULL        NULL        NULL        NULL        NULL    
G           NULL        NULL        NULL        NULL        80          NULL        NULL        NULL        NULL

How can I modify the above table into as shown below by using query, Please help! 

ClientID    Balance1    Balance2    Balance3    Balance4    Balance5    Balance6    Balance7    Balance8    Balance9
A           NULL        NULL        NULL        3           3           3           3           3           3   
B           10          10          10          10          20          20          20          20          20
C           Null        8           8           10          10          10          1           1           1
D           Null        19          19          19          19          19          19          19          19
E           Null        Null        50          50          50          50          50          50          50          
F           NULL        NULL        NULL        30          30          30          30          30          30          
G           NULL        NULL        NULL        NULL        80          80          80          80          80

Upvotes: 0

Views: 56

Answers (3)

JohnLBevan
JohnLBevan

Reputation: 24470

Solution:

update myTable
set balance9 = coalesce(balance9 ,balance8 ,balance7 ,balance6 ,balance5 ,balance4 ,balance3 ,balance2 ,balance1)
,   balance8 = coalesce(balance8 ,balance7 ,balance6 ,balance5 ,balance4 ,balance3 ,balance2 ,balance1)
,   balance7 = coalesce(balance7 ,balance6 ,balance5 ,balance4 ,balance3 ,balance2 ,balance1)
,   balance6 = coalesce(balance6 ,balance5 ,balance4 ,balance3 ,balance2 ,balance1)
,   balance5 = coalesce(balance5 ,balance4 ,balance3 ,balance2 ,balance1)
,   balance4 = coalesce(balance4 ,balance3 ,balance2 ,balance1)
,   balance3 = coalesce(balance3 ,balance2 ,balance1)
,   balance2 = coalesce(balance2 ,balance1)

Working Example:

declare @myTable table 
(
    clientId nchar(1) not null primary key clustered
    , balance1 int null
    , balance2 int null
    , balance3 int null
    , balance4 int null
    , balance5 int null
    , balance6 int null
    , balance7 int null
    , balance8 int null
    , balance9 int null
)

insert into @myTable (clientId, balance3, balance6)
values ('X',8,12)

update @myTable
set balance9 = coalesce(balance9 ,balance8 ,balance7 ,balance6 ,balance5 ,balance4 ,balance3 ,balance2 ,balance1)
,   balance8 = coalesce(balance8 ,balance7 ,balance6 ,balance5 ,balance4 ,balance3 ,balance2 ,balance1)
,   balance7 = coalesce(balance7 ,balance6 ,balance5 ,balance4 ,balance3 ,balance2 ,balance1)
,   balance6 = coalesce(balance6 ,balance5 ,balance4 ,balance3 ,balance2 ,balance1)
,   balance5 = coalesce(balance5 ,balance4 ,balance3 ,balance2 ,balance1)
,   balance4 = coalesce(balance4 ,balance3 ,balance2 ,balance1)
,   balance3 = coalesce(balance3 ,balance2 ,balance1)
,   balance2 = coalesce(balance2 ,balance1)

select * from @myTable

Upvotes: 2

LittleBobbyTables - Au Revoir
LittleBobbyTables - Au Revoir

Reputation: 32720

You'll need to update each column individually, and sequentially, like this:

UPDATE YourTable
SET Balance2 = Balance1 
WHERE Balance2 IS NULL AND Balance1 IS NOT NULL

UPDATE YourTable
SET Balance3 = Balance2
WHERE Balance3 IS NULL AND Balance2 IS NOT NULL

To make sure that either all of your updates occur, or none of your updates occur, you may want to wrap all of your statements in a transaction.

Upvotes: 2

Seth Flowers
Seth Flowers

Reputation: 9180

Learn about the UPDATE statement.

You need a few statements like the following:

UPDATE
    [YourTableName]
SET
    [Balance5] = '3',
    [Balance6] = '3',
    [Balance7] = '3',
    [Balance8] = '3',
    [Balance9] = '3'
WHERE
    [ClientID] = 'A'

Make sure you have an appropriate WHERE clause, otherwise you might be updating more data than you intended. It can be a good practice to wrap the update queries in a TRAN that you initially rollback, to make sure you are getting an expected rowcount.

Upvotes: 0

Related Questions