SQL: how to update multiple fields, so the empty content of the field moves to the last logical columns - they lose the empty address lines

Question

I have three columns of the address bar, aline1, aline2, aline3 for street address. According to incompatible data, any or all of them may be empty. I want to transfer the first non-empty to addrline1, the second non-empty to addrline2 and clear line 3 if there are no three non-empty lines, otherwise leave it. (“First” means that aline1 is first if it is not empty, aline2 is first if aline1 is empty, aline3 is first if aline1 and 2 are both empty)

Rows in this staging table do not have a key, and there may be duplicate rows. I could add a key.

Apart from the statement of the big case, which lists the possible combination of empty and not empty and moves the fields around, how can I update the table? (The same problem occurs much more than 3 lines, so I do not want to use the case statement)

I am using Microsoft SQL Server 2008

Answer 1

You can create insert and update triggers that check if fields are empty and then move them.

Answer 2

Another alternative. It uses an undocumented function %%physloc%%to work without a key. You would be much better off adding the key to the table.

CREATE TABLE #t
(
aline1 VARCHAR(100), 
aline2 VARCHAR(100), 
aline3  VARCHAR(100)
)

INSERT INTO #t VALUES(NULL, NULL, 'a1')
INSERT INTO #t VALUES('a2', NULL, 'b2')

;WITH cte
     AS (SELECT *,
                 MAX(CASE WHEN RN=1 THEN value END) OVER (PARTITION BY %%physloc%%) AS new_aline1,
                 MAX(CASE WHEN RN=2 THEN value END) OVER (PARTITION BY %%physloc%%) AS new_aline2,
                 MAX(CASE WHEN RN=3 THEN value END) OVER (PARTITION BY %%physloc%%) AS new_aline3
         FROM   #t
                OUTER APPLY (SELECT ROW_NUMBER() OVER (ORDER BY CASE WHEN value IS NULL THEN 1 ELSE 0 END, idx) AS
                                   RN, idx, value
                             FROM   (VALUES(1,aline1),
                                           (2,aline2),
                                           (3,aline3)) t (idx, value)) d)
UPDATE cte
SET    aline1 = new_aline1,
       aline2 = new_aline2,
       aline3 = new_aline3  


SELECT *
FROM #t

DROP TABLE #t

Answer 3

, ,

create table taddress (id int,a varchar(10),b varchar(10),c varchar(10));
insert taddress
select 1,1,2,3 union all
select 2,1, null, 3 union all
select 3,null, 1, 2 union all
select 4,null,null,2 union all
select 5,1, null, null union all
select 6,null, 4, null

,

;with tmp as (
    select *, rn=ROW_NUMBER() over (partition by t.id order by sort)
    from taddress t
    outer apply
    (
        select 1, t.a where t.a is not null union all
        select 2, t.b where t.b is not null union all
        select 3, t.c where t.c is not null
            --- EXPAND HERE
    ) u(sort, line)
)
select t0.id, t1.line, t2.line, t3.line
from taddress t0
left join tmp t1 on t1.id = t0.id and t1.rn=1
left join tmp t2 on t2.id = t0.id and t2.rn=2
left join tmp t3 on t3.id = t0.id and t3.rn=3
--- AND HERE
order by t0.id

EDIT -

;with tmp as (
    select *, rn=ROW_NUMBER() over (partition by t.id order by sort)
    from taddress t
    outer apply
    (
        select 1, t.a where t.a is not null union all
        select 2, t.b where t.b is not null union all
        select 3, t.c where t.c is not null
            --- EXPAND HERE
    ) u(sort, line)
)
UPDATE taddress
set a = t1.line,
    b = t2.line,
    c = t3.line
from taddress t0
left join tmp t1 on t1.id = t0.id and t1.rn=1
left join tmp t2 on t2.id = t0.id and t2.rn=2
left join tmp t3 on t3.id = t0.id and t3.rn=3

Answer 4

- . Case

.

With Inputs As
    (
    Select PK, 1 As LineNum, aline1 As Value
    From StagingTable
    Where aline1 Is Not Null
    Union All
    Select PK, 2, aline2
    From StagingTable
    Where aline2 Is Not Null
    Union All
    Select PK, 3, aline3
    From StagingTable
    Where aline3 Is Not Null
    )
    , ResequencedInputs As
    (
    Select PK, Value
        , Row_Number() Over( Order By LineNum ) As LineNum
    From Inputs
    )
    , NewValues As
    (
    Select S.PK
        , Min( Case When R.LineNum = 1 Then R.addrline1 End ) As addrline1
        , Min( Case When R.LineNum = 2 Then R.addrline1 End ) As addrline2
        , Min( Case When R.LineNum = 3 Then R.addrline1 End ) As addrline3
    From StagingTable As S
        Left Join ResequencedInputs As R
            On R.PK = S.PK
    Group By S.PK
    )
Update OtherTable
Set addrline1 = T2.addrline1
    , addrline2 = T2.addrline2
    , addrline3 = T2.addrline3
From OtherTable As T
    Left Join NewValues As T2
        On T2.PK = T.PK

Answer 5

R. . , , - . , . , , , , , . , , , , SQL , . "" , . , ( ) , .

, , - , collatDesc1, collatCode1, collatLastAppraisal1,... collatLastAppraisal5, , excludeand, , , .

%% phsloc %% - - , ( ). , , .

FWIW, , , , . , " ", .

create trigger fixit on lines 
instead of insert as
declare @maybeblank1 as varchar(max)
declare @maybeblank2 as varchar(max)
declare @maybeblank3 as varchar(max)


set @maybeBlank1 = (select line1 from inserted)
set @maybeBlank2 = (select line2 from inserted)
set @maybeBlank3 = (select line3 from inserted)

declare @counter int
set @counter = 0 

while @counter < 3
begin
    set @counter = @counter + 1
    if @maybeBlank2 = '' 
        begin
            set @maybeBlank2  =@maybeblank3
            set @maybeBlank3 = ''
        end
    if @maybeBlank1 = ''
        begin
            set @maybeBlank1 = @maybeBlank2
            set @maybeBlank2 = ''
        end
end
select * into #kludge from inserted
update #kludge
    set line1 = @maybeBlank1,
    line2 = @maybeBlank2,
    line3 = @maybeBlank3
insert into lines 
    select * from #kludge