如何改进此邮件地址SQL Server SELECT语句？

这样做的方法是使用UNPIVOT.这是解决方案:

With AddrTable as (
Select AddrFld, MailAddr From (
Select Cast(ISNULL([Line1], '') as Varchar(102)) as [A1], 
       Cast(ISNULL([Line2], '') as Varchar(102)) as [A2], 
       Cast(ISNULL([Line3], '') as Varchar(102)) as [A3], 
       Cast(ISNULL(LTRIM(RTRIM(City)),'') + ' ' + ISNULL(LTRIM(RTRIM(RegionCode)),'') + '  ' + ISNULL(LTRIM(RTRIM(PostalCode)),'') as Varchar(102)) as A4
From TableName Where UniqueID=@UniqueID) p
Unpivot (MailAddr For AddrFld in ([A1], [A2], [A3], [A4])) as unpvt)
Select Row_Number() over (Order by (Case Len(MailAddr) When 0 then 1 else 0 end), AddrFld) as RN, 
MailAddr From AddrTable 
Order By RN

这是输出:

Address1
Westby WI  55555
-empty line-
-empty line-

请注意,我必须使用"Varchar(102)"作为字段长度(unpivot要求所有字段都相同),因为您的城市/地区/邮政总共最多可以有102个字符.另请注意,"@ UniqueID"是您需要的记录的标识符.这将返回四行,总是四行,其中包含您的地址所需的数据.

更新:如果您需要将其作为一组四列而不是四行返回,则只需将其放入视图中,然后使用 Pivot查询视图.我在这里包含了视图的完整性,因为我必须在创建视图时稍微改变上面的内容,因此包含了uniqueID字段并且没有进行排序(排序现在在查询中完成):

Create View AddressRows AS
 With AddrTable as (
 Select UniqueID, AddrFld, MailAddr From (
 Select UniqueID, 
       Cast(ISNULL([Line1], '') as Varchar(102)) as [A1], 
       Cast(ISNULL([Line2], '') as Varchar(102)) as [A2], 
       Cast(ISNULL([Line3], '') as Varchar(102)) as [A3], 
       Cast(ISNULL(LTRIM(RTRIM(City)),'') + ' ' + ISNULL(LTRIM(RTRIM(RegionCode)),'') + '  ' + ISNULL(LTRIM(RTRIM(PostalCode)),'') as Varchar(102)) as A4
 From TableName Where UniqueID=@UniqueID) p
 Unpivot (MailAddr For AddrFld in ([A1], [A2], [A3], [A4])) as unpvt)
 Select UniqueID, 
       Row_Number() over (Order by (Case Len(MailAddr) When 0 then 1 else 0 end), AddrFld) as RN, 
       MailAddr From AddrTable 

然后,当你想拉出匹配的"行"时,使用这个SQL将其转回(注意我使用UniqueID再次查询):

Select [Addr1], [Addr2], [Addr3], [Addr4] From (
Select Top 4 'Addr' + Cast(Row_Number() over (Order by RN) as Varchar(12)) as AddrCol,  -- "Top 4" needed so we can sneak the "Order By" in 
MailAddr 
From AddressRows Where UniqueID=@UniqueID
) p PIVOT (Max([MailAddr]) for AddrCol in ([Addr1], [Addr2], [Addr3], [Addr4])
) as pvt

返回:

Addr1            Addr2                Addr3           Addr4
--------------   ------------------   -------------   ------------------ 
Address1         Westby WI  54667