今天在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时候会报错,可以先将这些字符替换成其他的,在最终结果中再替换回来。