今天在sqlserver2012上遇到一个分析需求，用到动态列转行，记录一下：

      create table t1 (id int,value varchar(100));  
      insert into t1 values(1,'a,b,c');  
      insert into t1 values(2,'a,b,c,d,e');  
      insert into t1 values(3,'a,b,c,d,e,f,g');  
      select * from t1;  
      ----------------
      1   a,b,c  
      2   a,b,c,d,e  
      3   a,b,c,d,e,f,g

      create table t1 (id int,value varchar(100));  
          SELECT a.id,b.[values]   
    FROM (   
                SELECT id,[VALUE] = CONVERT(XML , '' + REPLACE(value , ',' , '') + '')   
                FROM t1    
             ) a  
    OUTER APPLY   
            ( SELECT  [values] = N.v.value('.' , 'nvarchar(max)')  
               FROM    a.[value].nodes('/v') N ( v )  
          ) b  

结果：

1 a
1 b
1 c
2 a
2 b
2 c
2 d
2 e
3 a
3 b
3 c
3 d
3 e
3 f
3 g

另外，要注意，如果value字段中有诸如&等在xml为特殊字符的字符，需要注意使用CONVERT(XML时候会报错，可以先将这些字符替换成其他的，在最终结果中再替换回来。

如果觉得本博客对您有帮助，请 赞助作者 。

转载请注明：lxw的大数据田地 » sqlserver 动态列转行