如何使用SQL合并拆分数据

debugcn 发表于 Dev

DNac

原始答案：
我需要按指定的分隔符分割字符串。我已经通过SQL性能指南-T-SQL查询-使用拆分字符串实现了这一点Numbers Table。但是，一旦字符串被拆分，如何将其合并回列中？因此，以这种形式分割字符串的结果是：

**Item**
a
id
xyz
abcd
a
id
xyz
abcd

转换为以下形式：

C1 C2  C3   C4
a  id  xyz  abc
a  id  xyz  abc

等等？

根据山羊的答案进行更新：

/abc/def/ghi/jkl.html_abc_def

应该分成

C1   C2   C3   C4   C5
abc  def  ghi  jkl  html_abc_def

这就是我到目前为止

WITH CTE AS (SELECT testString = 'abc/def/ghi/jkl.html_abc_def'
            ),
     splitString
     AS (SELECT *,
         CONVERT(XML, '<String><Section>'
                      + Replace(Replace((substring(teststring,1,patindex('%.html%',teststring)-1)),'_','/'), '/', '</Section><Section>')
                      + '</Section></String>') AS xmlString
         FROM   CTE)
SELECT xmlstring.value('/String[1]/Section[1]', 'varchar(100)') AS Col1,
       xmlstring.value('/String[1]/Section[2]', 'varchar(100)') AS Col2,
       xmlstring.value('/String[1]/Section[3]', 'varchar(100)') AS Col3,
       xmlstring.value('/String[1]/Section[4]', 'varchar(100)') AS Col4,
       xmlstring.value('/String[1]/Section[5]', 'varchar(100)') AS Col5,
       xmlstring.value('/String[1]/Section[6]', 'varchar(100)') AS Col6
FROM   splitstring;

--select substring(teststring,1,patindex('%.html%',teststring)-1) first
--substring(teststring,patindex('%.html%',teststring)+1,200) second

第一个子字符串将数据获取到.html，第二个子字符串将数据存储在其后。但是我不知道如何将它们放在一起。

知道了：

WITH CTE AS (SELECT testString = 'abc/def/ghi/jkl.html_abc_def'
            ),
     splitString
     AS (SELECT *,
         CONVERT(XML, '<String><Section>'
                      + Replace(Replace((substring(teststring,1,patindex('%.html%',teststring)-1)),'_','/'), '/', '</Section><Section>')
                      + '</Section></String>'
                      + '<String><Section>'
                      + substring(teststring,patindex('%.html%',teststring)+1,200)
                      + '</Section></String>'
                      ) AS xmlString
         FROM   CTE)
SELECT xmlstring.value('/String[1]/Section[1]', 'varchar(100)') AS Col1,
       xmlstring.value('/String[1]/Section[2]', 'varchar(100)') AS Col2,
       xmlstring.value('/String[1]/Section[3]', 'varchar(100)') AS Col3,
       xmlstring.value('/String[1]/Section[4]', 'varchar(100)') AS Col4,
       xmlstring.value('/String[2]/Section[1]', 'varchar(100)') AS Col5
FROM   splitstring;

哈特公司

您可以使用以下XML功能将定界的字符串拆分为单独的列，而不是先拆分为行：

;WITH cte AS (SELECT testString = 'test,1,2,3'
              UNION  SELECT 'test,2,3,4'
              UNION  SELECT 'test,5,6,7')
     ,SplitString AS (SELECT *,
                             CONVERT(XML,'<String><Section>'+ REPLACE(testString,',', '</Section><Section>') + '</Section></String>') AS xmlString
                      FROM cte
                      )    
 SELECT xmlString.value('/String[1]/Section[1]','varchar(100)') AS Col1
       ,xmlString.value('/String[1]/Section[2]','varchar(100)') AS Col2
       ,xmlString.value('/String[1]/Section[3]','varchar(100)') AS Col3
       ,xmlString.value('/String[1]/Section[4]','varchar(100)') AS Col4
 FROM SplitString

注意：以上使用逗号作为定界符，请相应地进行更改REPLACE()。

更新：很高兴您能对它进行排序，但是看起来您可能使XML创建过于复杂了，我认为您可以替换.为/然后/在第二个中使用REPLACE()：

;WITH cte AS (SELECT testString = 'abc/def/ghi/jkl.html_abc_def')
     ,SplitString AS (SELECT *,
                             CONVERT(XML,'<String><Section>'+ REPLACE(REPLACE(testString,'.','/'),'/', '</Section><Section>') + '</Section></String>') AS xmlString
                      FROM cte
                      )    
 SELECT xmlString.value('/String[1]/Section[1]','varchar(100)') AS Col1
       ,xmlString.value('/String[1]/Section[2]','varchar(100)') AS Col2
       ,xmlString.value('/String[1]/Section[3]','varchar(100)') AS Col3
       ,xmlString.value('/String[1]/Section[4]','varchar(100)') AS Col4
       ,xmlString.value('/String[1]/Section[5]','varchar(100)') AS Col5
 FROM SplitString

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。