微信搜索lxw1234bigdata | 邀请体验:数阅–数据管理、OLAP分析与可视化平台 | 赞助作者:赞助作者

sqlserver 动态列转行

数据仓库/数据库 lxw1234@qq.com 7478℃ 0评论

今天在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 动态列转行

喜欢 (2)
分享 (0)
发表我的评论
取消评论
表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址