sqlserver 动态列转行

今天在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

 

   

SELECT a.id,b.[values] 
FROM ( 
			SELECT id,[VALUE] = CONVERT(XML , '<v>' + REPLACE(value , ',' , '</v><v>') + '</v>') 
			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时候会报错，可以先将这些字符替换成其他的，在最终结果中再替换回来。