当显示为null时，使用大小写组合SQL列

Question

我正在尝试组合列，以便它们都显示为一列。问题是，如果一行中有一列为null，那么整行显示为null。我已经尝试使用CASE语句来完成此操作。这是我的问题：

SELECT
    a.AddressID,
    CASE
    WHEN a.Address IS NULL
    AND a.Address2 IS NULL THEN
        a.City + ' ' + a.State + ' ' + a.Country + ' ' + a.Zip
    WHEN a.Address2 IS NULL THEN
        a.Address + ' ' + a.City + ' ' + a.State + ' ' + a.Country + ' ' + a.Zip
    WHEN a.Zip IS NULL THEN
        a.Address + ' ' + a.Address2 + ' ' + a.City + ' ' + a.State + ' ' + a.Country
    WHEN a.City IS NULL
    AND a.Address2 IS NULL
    AND a.State IS NULL
    AND a.Country IS NULL
    AND a.Zip IS NULL THEN
        a.Address
    WHEN a.Address IS NULL
    AND a.Address2 IS NULL
    AND a.State IS NULL
    AND a.Zip IS NULL THEN
        a.City + ' ' + a.Country
    WHEN a.State IS NULL THEN
        a.Address + ' ' + a.Address2 + ' ' + a.City + ' ' + a.Country + ' ' + a.Zip
    WHEN a.City IS NULL
    AND a.Zip IS NULL THEN
        a.Address + ' ' + a.Address2 + ' ' + a.State + ' ' + a.Country
    WHEN a.Address IS NULL
    AND a.Address2 IS NULL
    AND a.City IS NULL
    AND a.Zip IS NULL THEN
        a.State + ' ' + a.Country
    WHEN a.State IS NULL
    AND a.Zip IS NULL THEN
        a.Address + ' ' + a.Address2 + ' ' + a.City + ' ' + a.Country
    WHEN a.Address IS NULL
    AND a.Address2 IS NULL
    AND a.City IS NULL THEN
        a.State + ' ' + a.Country + ' ' + a.Zip
    WHEN a.Address2 IS NULL
    AND a.State IS NULL THEN
        a.Address + ' ' + a.City + ' ' + a.Country + ' ' + a.Zip
    WHEN a.Address2 IS NULL
    AND a.Zip IS NULL THEN
        a.Address + ' ' + a.City + ' ' + a.State + ' ' + a.Country
    ELSE
        a.Address + ' ' + a.Address2 + ' ' + a.City + ' ' + a.State + ' ' + a.Country + ' ' + a.Zip
    END AS FullAddress
FROM
    Addresses a `

我的最终目标是在ASP.NET中填充一个下拉列表。如果有人知道在只有一列为null时让行返回null的另一种方法，请告诉我。我愿意采取另一种方法。此外，还有太多的行要走，并修复每一行，以便每一行为每一列都有一个值。非常感谢您的帮助。

Answer 1

通过使用IsNull()和NULLIF()函数，您可以实现您想要的输出。

试试这个

SELECT  a.AddressID,
        ISNULL(NULLIF(a.Address + ' ', ''), '') + 
        ISNULL(NULLIF(a.Address2 + ' ', ''), '') +
        ISNULL(NULLIF(a.City + ' ', ''), '') + 
        ISNULL(NULLIF(a.State + ' ', ''), '') + 
        ISNULL(NULLIF(a.Country + ' ', ''), '') + 
        ISNULL(NULLIF(a.Zip, ''), '') AS FullAddress
    FROM
        Addresses a

还有一件事，如果您使用的是SQL Server 2012及更高版本，那么您可以使用CONCAT()函数。为此，我在我的博客中写了一篇博客文章-krishnrajana.wordpress.com

Answer 2

不确定null时额外空间的存在是否重要？如果是，则使用IsNull（NullIf（approad.

SELECT
    a.AddressID, 
    coalesce(a.Address, ' ') + ' ' + 
    coalesce(a.Address2, ' ') + ' ' + 
    coalesce(a.State, ' ') + ' ' + 
    coalesce(a.Country, ' ') + ' ' + 
    coalesce(a.Zip, '') as FullAddress
FROM Addresses a        

Answer 3

您只需要使用联合：

Select a.AddressID
    , RTRIM(
        coalesce(a.Address+' ', '')
        + coalesce(a.Address2+' ', '')
        + coalesce(a.City+' ', '')
        + coalesce(a.State+' ', '')
        + coalesce(a.Country+' ', '')
        + coalesce(a.Zip+' ', '')
    )
From Addresses a;

它将在每个非空值之后添加1个空格，并且值之间只有1个空格。

如果最后一个值为空，RTRIM将删除末尾的空格。

样本：

Select '-'+RTRIM(
        coalesce(x+' ', '')
        + coalesce(y+' ', '')
        + coalesce(z+' ', '')
    )+'-'
From (values
    ('a', 'b', 'c')
    , ('a', null, 'c')
    , ('a', 'b', null)
) as v(x, y, z)

样本输出：

-a b c-
-a c-
-a b-