SQL SERVER 逗号分隔列进行行列转换

在现实应用中，有时我们需要用到将数据库表中逗号分隔的列，进行列转行；现在列举出其中比较常用的一种，利用XML来转换（利用XML进行列转行）。

 

如下图：数据库表中，一列或者多列有都好分隔的数据转换为行数据。

首先我们先看看转换前后对比：

 

    转换前：   ------>     转换后：

 

转换过程如下：

 

--1，准备工作：先创先使用的表，并插入数据

CREATE TABLE #TMP
(
      FIX NVARCHAR(20 ),
      CODE1 NVARCHAR(200 ),
      CODE2 NVARCHAR(200 )
)
 
INSERT INTO #TMP VALUES ( 'A001','001' ,'AAA,BBB,CCC')
INSERT INTO #TMP VALUES ( 'A002','002,003' ,'AAA,BBB,CCC')
--SELECT * FROM  #TMP -- 查看数据

 

 

 --2中间过程，现将表转换为 XML格式

SELECT
    T.FIX
    ,CODE1 = CONVERT(XML ,'<V>'+ REPLACE(T .CODE1, ',','</V><V>' )+'</V>')  
    ,CODE2 = CONVERT(XML ,'<V>'+ REPLACE(T .CODE2, ',','</V><V>' )+'</V>')  
FROM #TMP T

 

--3拆分过程，拆分结果

SELECT  
    T .FIX
    ,A. X.value ('.', 'VARCHAR(20)') AS CODE1
    ,B. X.value ('.', 'VARCHAR(20)') AS CODE2
FROM
     (  
          SELECT
               T.FIX
              ,CODE1 = CONVERT(XML ,'<V>'+ REPLACE(T .CODE1, ',','</V><V>' )+'</V>')  
              ,CODE2 = CONVERT(XML ,'<V>'+ REPLACE(T .CODE2, ',','</V><V>' )+'</V>')  
          FROM #TMP T
     )
     AS T  
     CROSS APPLY CODE1.nodes ('//V') AS A (X)   
     CROSS APPLY CODE2.nodes ('//V') AS B (X)   
ORDER BY T.FIX

 

--4 删除临时表

DROP TABLE #TMP

 

将逗号分隔列，进行列转行的方法应该还有其他，有兴趣的可以研究下。