如果列中包含逗号分隔的值，请另作一行

小编典典

如果列中包含逗号分隔的值，请另作一行

sql

假设我们有一个简单的选择查询，返回的结果如下所示

   FirstName          LastName                     PayScale
   -----------        ------------                ---------------
    Craig              L                             150000
    Alice,Lisa         simons                        100000

因此，如果我们用逗号分隔名字，那么我想要这样的结果

   FirstName          LastName                     PayScale
   -----------        ------------                ---------------
    Craig              L                             150000
    Alice              simons                        100000
    Lisa               simons                        100000

阅读 200

2021-04-14

共1个答案

小编典典

您可以将查询编写为：

DECLARE @employee TABLE (FirstName  VARCHAR(100),
                         LastName   VARCHAR(100),
                         PayScale INT)

INSERT INTO @employee VALUES 
('Craig','L',150000) , 
('Alice,Lisa','simons',100000)



SELECT Split.a.value('.', 'VARCHAR(100)') AS FirstName  , LastName  ,PayScale
       FROM  (SELECT  CAST ('<M>' + REPLACE(FirstName, ',', '</M><M>') + '</M>' 
                             AS XML) AS FirstName ,  LastName    ,PayScale
               FROM  @employee) 
       AS A 
CROSS APPLY FirstName.nodes ('/M') AS Split(a)

2021-04-14