另一种选择。它使用未记录的%%physloc%%函数在没有键的情况下工作。向表中添加键会更好。

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

这里有一个替代方案
样表进行讨论，不用担心无意义的数据，它们只需要为空或不为

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

Update -已将语句更改为Update语句。已删除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

R.A. Cyberkiwi、托马斯和Martin，非常感谢--你们每个人的回答都非常慷慨。所有这些答案都是我所寻找的填鸭式的答案。我要说，它们都依赖于一种类似钥匙的装置，通过将地址分成几行来工作，有些是空的，有些不是，不包括空的。在地址行的情况下，在我看来，这是一个语义上的花招，目的是使问题适合SQL所擅长的，而不是一个自然的方法来概念化问题。地址行并不是表中“真正”独立的行，只是为了一个报表而被反规范化。但这是有争议的，不管您同意与否，我（一个普通的初学者）认为你的每一个选择都是值得详细说明和研究的惯用解决方案。
我还遇到了许多类似的情况，其中确实需要进行规范化-例如，collatDesc 1、collatCode 1、collatLastAppraisal 1、... collatLastAppraisal 5，它们使用了比地址更复杂的标准来排除哪些内容以及如何排序，我认为从您的回答中获得的技术会有所帮助。
%%phsloc%%很有趣-因为我可以在这种情况下创建一个密钥，所以我不会使用它（正如Martin建议的那样）。Martin的东西中还有其他我不熟悉的东西，我仍然在到处扔。
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

您 可以 创建 一 个 插入 和 更新 触发 器 来 检查 字段 是否 为 空 ， 然后 移动 它们 。