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”是您需要其地址的记录的标识符。这将返回四个总是4 行 包含您需要为您的地址中的数据。
如果需要将其作为一组四 列 而不是四行返回，则只需将其放入视图中，然后使用 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
602392714
清零编程群