分列技术实现详解.doc

分列实现技术工作中有时会遇到表中列按照某个分割符将不同属性值串连在一起，为了重新组织数据，要求将该值按属性值分列存储，因此需要采取一定的技术手段，对原列进行分割，下面以一个实例详细讲述分列实现的技术要点。一、数据准备首先用以下代码创建tp表：use tempdb;goif object_id(dbo.tp,u) is not null drop table tp;create table dbo.tp( t_id int primary key, t_val varchar(max);goinsert into tp values (1,许建宏,1,20030722本人,001101343), (2,梅春兰,2,19650409户主,001101634), (3,管玲芳,2,19470908户主,001101651), (4,蒋降,2,19840927户主,001101653), (5,周连香,2,19450715户主,001101660), (6,徐爱英,2,19551125户主,001101666), (7,倪兵,1,19730218户主,001101671);Tp表t_val列，将姓名、性别、出生日期、身份证号、与户主关系及户号以逗号分割并串连在一起，要求将该列按各个属性值单独列示。二、实现步骤1、首先创建一张辅助数字表，仅包含一列整型数值（120），代码如下：if OBJECT_ID(dbo.num,u) is not null drop table dbo.num;create table dbo.num(val int primary key);go with ias(select 1 n union all select n+1 from i where n100 ) insert into dbo.num select * from i; 利用递归技术快速向num表插入1100个数值，如果要插入更多的数值，需要指定option选项，默认查询最大深度为100。2、查找每个逗号在列中的位置（注意：如果t_val列是unicode符，需要要除以2）select t.t_id , t.t_val , n.val from tp t join num n on n.val=datalength(t.t_val) and SUBSTRING(t.t_val,n.val,1)=,;结果如下：上述val列标明了每个逗号所在的位置，t_val列应拆分为6列，结果中只生成了5行，可在t_val列值前添加一个逗号，每个t_id将生成6行数据：在t_val值前加上逗号后，结果集中的val列确定了非逗号的起始位置，即需要提取位置的起点，利用类似的方法，将t_val值后面加上逗号，利用差值计算出两分割逗号之间需提取字符的数量，示例代码如下： select t.t_id , t.t_val , n.val , substring(t_val,val,charindex(,t_val+,val)-val) col from tp t join num n on n.val=datalength(t.t_val)+1and SUBSTRING(,+t.t_val,n.val,1)=,;结果如下：综上1、2所述，中间代码如下： select t.t_id , t.t_val , n.val , ROW_NUMBER() over(partition by t_id order by n.val) sq , substring(t_val,val,charindex(,t_val+,val)-val) col from tp t join num n on n.val=datalength(t.t_val)+1and SUBSTRING(,+t.t_val,n.val,1)=,;结果如下图所示：上述结果正确地从t_val列中提取了分割的值，sq列按统一的行号指明了分割的顺序，将上述结果生成CTE，再对此进行行转列：with c as ( select t.t_id , t.t_val , n.val , ROW_NUMBER() over(partition by t_id order by n.val) sq , substring(t_val,val,charindex(,t_val+,val)-val) col from tp t join num n on n.val=datalength(t.t_val)+1 and SUBSTRING(,+t.t_val,n.val,1)=, ) select t_id , t_val , MAX(case sq when 1 then col end) as 姓名 , MAX(case sq when 2 then cast(col AS int) end) as 性别 , max(case sq when 3 then CAST(col as varchar(8) end) as 出生日期 , MAX(case sq when 4 then cast(col as char(18) end) as 身份证号 , MAX(case sq when 5 then col end) as 与户主关系 , MAX(case sq when 6 then cast(col as char(9) end) as 户编号 from c group by t_id,t_val;最终结果如下：三、其他方法Excel 2007及以上版本中提供