SQL Server 2014用字典数据替换逗号分隔字符串

Question

我正在运行一个查询，向我显示某些项目的ID。我知道每个项目的描述。因此，我有2列运行查询

Select 
    IdItem, 
    case
       when IdItem = 'I'
          then 'Injection'
       when IdItem = 'P'
          then 'Pill'
       when IdItem = 'DI'
          then 'DIU'
       when IdItem = 'F'
          then 'AQVF'
       when IdItem = 'M'
          then 'AQVM'
       when IdItem = 'O'
          then 'Other'
       else 'Not Defined'
    end as 'Description'
from 
    dbo.Items

问题是我的结果有时会向我展示一个逗号分开的结果。

类似的东西：

  IdItem | Description  
---------+------------
       P | Pill    
    M, O | Not Defined     
      DI | DIU      
M, P, DI | Not Defined

结果显示了描述未定义的，由于其他原因，结果是否有这样的方法。

  IdItem | Description  
---------+----------------
       P | Pill    
    M, O | AQVM, Other     
      DI | DIU      
M, P, DI | AQVM, Pill, DIU

我必须对字符串使用替换，或者必须在外壳上制作每个组合

Select IdItem
, case
when IdItem = 'I'
then 'Injection'
when IdItem = 'P'
then 'Pill'
when IdItem = 'DI'
then 'DIU'
when IdItem = 'F'
then 'AQVF'
when IdItem = 'M'
then 'AQVM'
when IdItem = 'O'
then 'Other'
when IdItem = 'M, O'
then 'AQVM, Other'
when IdItem = 'M, P, DI'
then 'AQVM, Pill, DIU'
when IdItem = ...all other combinations
then ...results
else 'Not Defined'
end
as 'Description'
 from dbo.Items

Answer 1

这些操作通常用一个使用定义的定界符拆分字符串值的函数来解决。如果您不在SQL Server 2016 上，并且无法使用string_split中的内置，则必须自己滚动。我个人使用：

create function [dbo].[StringSplit]
(
    @str nvarchar(4000) = ' '               -- String to split.
    ,@delimiter as nvarchar(1) = ','        -- Delimiting value to split on.
    ,@num as int = null                     -- Which value to return.
)
returns table
as
return
(                       -- Start tally table with 10 rows.
    with n(n)   as (select n from (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n(n))
                        -- Select the same number of rows as characters in @str as incremental row numbers.
                    -- Cross joins increase exponentially to a max possible 10,000 rows to cover largest @str length.
        ,t(t)   as (select top (select len(@str) a) row_number() over (order by (select null)) from n n1,n n2,n n3,n n4)
                            -- Return the position of every value that follows the specified delimiter.
        ,s(s)   as (select 1 union all select t+1 from t where substring(@str,t,1) = @delimiter)
                            -- Return the start and length of every value, to use in the SUBSTRING function.
                    -- ISNULL/NULLIF combo handles the last value where there is no delimiter at the end of the string.
        ,l(s,l) as (select s,isnull(nullif(charindex(@delimiter,@str,s),0)-s,4000) from s)
    select rn as ItemNumber
            ,Item
    from(select row_number() over(order by s) as rn
                ,substring(@str,s,l) as item
        from l
        ) a
    where rn = @num         -- Return a specific value where specified,
        or @num is null     -- Or the everything where not.
)

使用如下：

declare @Items table(IdItem nvarchar(50));
insert into @Items values('P'),('M, O'),('DI'),('M, P, DI, ZZZ'),('ZZZ');
declare @ItemDescripions table(ItemId nvarchar(50),ItemDescription nvarchar(50));
insert into @ItemDescripions values('I' ,'Injection'),('P' ,'Pill'),('DI','DIU'),('F' ,'AQVF'),('M' ,'AQVM'),('O' ,'Other');
select IdItem
    ,ltrim(rtrim(s.Item)) as Item
    ,isnull(d.ItemDescription,'Not Defined') as ItemDescription
from @Items i
    outer apply dbo.StringSplit(i.IdItem,',',null) s
    left join @ItemDescripions d
        on(ltrim(rtrim(s.Item)) = d.ItemId);

输出：

+---------------+------+-----------------+
|    IdItem     | Item | ItemDescription |
+---------------+------+-----------------+
| P             | P    | Pill            |
| M, O          | M    | AQVM            |
| M, O          | O    | Other           |
| DI            | DI   | DIU             |
| M, P, DI, ZZZ | M    | AQVM            |
| M, P, DI, ZZZ | P    | Pill            |
| M, P, DI, ZZZ | DI   | DIU             |
| M, P, DI, ZZZ | ZZZ  | Not Defined     |
| ZZZ           | ZZZ  | Not Defined     |
+---------------+------+-----------------+

---

如果您想与问题中的问题完全输出ItemDescription值(我建议不要在不将数据存储在分界列表中存储数据的最佳实践(，您可以使用con stuff和 for xml Combo而是：

select i.IdItem
        ,stuff((select ', ' + isnull(d.ItemDescription,'Not Defined')
                from dbo.StringSplit(i.IdItem,',',null) s
                    left join @ItemDescripions d
                        on(ltrim(rtrim(s.Item)) = d.ItemId)
                order by s.ItemNumber
                for xml path('')
                )
            ,1,2,'') as ItemDescription
from @Items i;

输出：

+---------------+------------------------------+
|    IdItem     |       ItemDescription        |
+---------------+------------------------------+
| P             | Pill                         |
| M, O          | AQVM, Other                  |
| DI            | DIU                          |
| M, P, DI, ZZZ | AQVM, Pill, DIU, Not Defined |
| ZZZ           | Not Defined                  |
+---------------+------------------------------+