使用SQL将两列的逗号分隔值映射到新行

Question

ID        NAME          COMMENTS
1          ABC           HI
2,3        DEF,GHI       HELLO
4          JKL           HII
5,6,7      M,N,O         HEY

我已经在我的表中用逗号分隔的值显示了上述数据，这些逗号分隔的值是按顺序映射的。就像。id 1与ABC相关，2与DEF相关，3与GHI等相关。

我想使用sql查询获得以下输出：

ID        NAME      COMMENTS
1          ABC        HI
2          DEF        HELLO
3          GHI        HELLO
4          JKL        HII
5          M          HEY
6          N          HEY
7          O          HEY

Answer 1

在Server 2016+中，您可以使用string_split()。

在Server 2016之前，您需要一些其他方式来拆分字符串。使用Jeff的CSV拆分表值函数：

select 
    id = i.item
  , name = n.item
  , comments = t.comments
from t
  cross apply dbo.delimitedsplit8k(t.id,',') i
  cross apply dbo.delimitedsplit8k(t.name,',') n
where i.itemnumber = n.itemnumber

rextester 演示：http://rextester.com/EEFRS3810

返回：

+----+------+----------+
| id | name | comments |
+----+------+----------+
|  1 | ABC  | HI       |
|  2 | DEF  | HELLO    |
|  3 | GHI  | HELLO    |
|  4 | JKL  | HII      |
|  5 | M    | HEY      |
|  6 | N    | HEY      |
|  7 | O    | HEY      |
+----+------+----------+

分裂字符串引用：

• 泰利哦！一种改进的SQL 8K“CSV分离器”函数-- Jeff Moden
• 分裂弦:后续-亚伦·伯特兰
• 用正确的方式分割字符串--或者是次好的方式--亚伦伯特兰
• 在Server 2016中:追踪#1 - Aaron Bertrand

使用的内联xml字符串拆分器：

select 
    id = i.RetVal
  , name = n.RetVal
  , comments = t.comments
from t
   Cross Apply (
                Select RetSeq = Row_Number() over (Order By (Select null))
                      ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
                From  (Select x = Cast('<x>' + replace((Select replace(t.[Id],',','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as x
                Cross Apply x.nodes('x') AS B(i)
             ) i
   Cross Apply (
                Select RetSeq = Row_Number() over (Order By (Select null))
                      ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
                From  (Select x = Cast('<x>' + replace((Select replace(t.[Name],',','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as x
                Cross Apply x.nodes('x') AS B(i)
             ) n
where i.RetSeq = n.RetSeq

rextester演示：http://rextester.com/ZCKVNV72753

Answer 2

如果有4个或更少分隔项，则可以使用parsename将其拆分。对于4个或更少的项，这是可用的最快的方法：

样本数据与解决方案

-- Sample data
DECLARE @yourTable TABLE
(
  ID       varchar(100),
  Name     varchar(100),
  Comments varchar(100)
);

INSERT @yourTable VALUES 
  ('1','ABC','HI'),('2,3','DEF,GHI','HELLO'),('4','JKL','HII'),('5,6,7','M,N,O','HEY');

-- solution using parsename for splitting
SELECT 
  ID   = id_new,
  Name = name_new,
  Comments
FROM @yourTable
CROSS JOIN (VALUES (1),(2),(3),(4)) t(N)
CROSS APPLY 
(VALUES 
  (parsename(REPLACE(ID,',','.'), t.N), parsename(REPLACE(Name,',','.'), t.N))
) AS x(id_new, name_new)
WHERE id_new IS NOT NULL
ORDER BY id_new; -- for presentation only, not required and will slow you down

结果

ID   Name  Comments
---- ----- -----------
1    ABC   HI
2    DEF   HELLO
3    GHI   HELLO
4    JKL   HII
5    M     HEY
6    N     HEY
7    O     HEY

执行计划

​