如果列包含逗号分隔值，请分别创建一个单独的行

I already asked a question here but its a bit change one

我已经在这里问了一个问题但是它有点改变了

FirstName          LastName                     PayScale
 -----------        ------------                ---------------
Alice,Lisa         simons,Jack                        100000

I want to see result like this

我想看到这样的结果

   FirstName          LastName                     PayScale
  -----------        ------------                ---------------
Alice              simons                        100000
Lisa               Jack                          100000

first value of firstname and first value of lastname will make a seperate row . In short want to comma separate multiple columns

firstname的第一个值和lastname的第一个值将成为一个单独的行。总之要用逗号分隔多个列

1 个解决方案

#1

As said by GarethD You should normalize your Database. This is a just a workaround

正如GarethD所说,您应该规范化您的数据库。这只是一种解决方法

create table #test (FirstName varchar(100),LastName varchar(100),PayScale int)

insert #test values
('Alice,Lisa','simons,Jack',100000)

SELECT FirstName,
       LastName,
       b.PayScale
FROM   (SELECT Split.a.value('.', 'VARCHAR(100)')FirstName,
               PayScale,
               Row_number()
                 OVER(
                   ORDER BY (SELECT 1))          rn
        FROM   (SELECT PayScale,
                       Cast ('<M>' + Replace(FirstName, ',', '</M><M>')
                             + '</M>' AS XML) AS FirstName
                FROM   #test) AS A
               CROSS APPLY FirstName.nodes ('/M') AS Split(a))fst
       JOIN (SELECT Split.a.value('.', 'VARCHAR(100)') LastName,
                    PayScale,
                    Row_number()
                      OVER(
                        ORDER BY (SELECT 1))           rn
             FROM   (SELECT PayScale,
                            Cast ('<M>' + Replace(LastName, ',', '</M><M>')
                                  + '</M>' AS XML) AS LastName
                     FROM   #test) AS A
                    CROSS APPLY LastName.nodes ('/M') AS Split(a)) b
         ON fst.rn = b.rn