标题：简单数据拆分(version 2.0)

 
 

  
  /*
  
  
标题：简单数据拆分(version 2.0)
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2010-05-07
地点：重庆航天职业学院
描述：

有表tb, 如下:
id          value
----------- -----------
1           aa,bb
2           aaa,bbb,ccc
欲按id,分拆value列, 分拆后结果如下:
id          value
----------- --------
1           aa
1           bb
2           aaa
2           bbb
2           ccc

  
  */
  
  


  
  --
  
  1. 旧的解决方法(sql server 2000)
  
  

  
  

  
  create
  
   
  
  table
  
   tb(id 
  
  int
  
  ,value 
  
  varchar
  
  (
  
  30
  
  ))

  
  insert
  
   
  
  into
  
   tb 
  
  values
  
  (
  
  1
  
  ,
  
  '
  
  aa,bb
  
  '
  
  )

  
  insert
  
   
  
  into
  
   tb 
  
  values
  
  (
  
  2
  
  ,
  
  '
  
  aaa,bbb,ccc
  
  '
  
  )

  
  go
  
  


  
  --
  
  方法1.使用临时表完成
  
  

  
  SELECT
  
   
  
  TOP
  
   
  
  8000
  
   id 
  
  =
  
   
  
  IDENTITY
  
  (
  
  int
  
  , 
  
  1
  
  , 
  
  1
  
  ) 
  
  INTO
  
   # 
  
  FROM
  
   syscolumns a, syscolumns b 


  
  SELECT
  
   A.id, value 
  
  =
  
   
  
  SUBSTRING
  
  (A.
  
  [
  
  value
  
  ]
  
  , B.id, 
  
  CHARINDEX
  
  (
  
  '
  
  ,
  
  '
  
  , A.
  
  [
  
  value
  
  ]
  
   
  
  +
  
   
  
  '
  
  ,
  
  '
  
  , B.id) 
  
  -
  
   B.id)

  
  FROM
  
   tb A, # B

  
  WHERE
  
   
  
  SUBSTRING
  
  (
  
  '
  
  ,
  
  '
  
   
  
  +
  
   A.
  
  [
  
  value
  
  ]
  
  , B.id, 
  
  1
  
  ) 
  
  =
  
   
  
  '
  
  ,
  
  '
  
  


  
  DROP
  
   
  
  TABLE
  
   #


  
  --
  
  方法2.如果数据量小，可不使用临时表
  
  

  
  select
  
   a.id , value 
  
  =
  
   
  
  substring
  
  (a.value , b.
  
  number
  
   , 
  
  charindex
  
  (
  
  '
  
  ,
  
  '
  
   , a.value 
  
  +
  
   
  
  '
  
  ,
  
  '
  
   , b.
  
  number
  
  ) 
  
  -
  
   b.
  
  number
  
  ) 

  
  from
  
   tb a 
  
  join
  
   master..spt_values  b 

  
  on
  
   b.type
  
  =
  
  '
  
  p
  
  '
  
   
  
  and
  
   b.
  
  number
  
   
  
  between
  
   
  
  1
  
   
  
  and
  
   
  
  len
  
  (a.value)

  
  where
  
   
  
  substring
  
  (
  
  '
  
  ,
  
  '
  
   
  
  +
  
   a.value , b.
  
  number
  
   , 
  
  1
  
  ) 
  
  =
  
   
  
  '
  
  ,
  
  '
  
  


  
  --
  
  2. 新的解决方法(sql server 2005)
  
  

  
  create
  
   
  
  table
  
   tb(id 
  
  int
  
  ,value 
  
  varchar
  
  (
  
  30
  
  ))

  
  insert
  
   
  
  into
  
   tb 
  
  values
  
  (
  
  1
  
  ,
  
  '
  
  aa,bb
  
  '
  
  )

  
  insert
  
   
  
  into
  
   tb 
  
  values
  
  (
  
  2
  
  ,
  
  '
  
  aaa,bbb,ccc
  
  '
  
  )

  
  go
  
  


  
  --
  
  方法1.使用xml完成
  
  

  
  SELECT
  
   A.id, B.value 
  
  FROM
  
  
(
  
  
  SELECT
  
   id, 
  
  [
  
  value
  
  ]
  
   
  
  =
  
   
  
  CONVERT
  
  (xml,
  
  '
  
  <root><v>
  
  '
  
   
  
  +
  
   
  
  REPLACE
  
  (
  
  [
  
  value
  
  ]
  
  , 
  
  '
  
  ,
  
  '
  
  , 
  
  '
  
  </v><v>
  
  '
  
  ) 
  
  +
  
   
  
  '
  
  </v></root>
  
  '
  
  ) 
  
  FROM
  
   tb
) A 
  
  OUTER
  
   APPLY
(
  
  
  SELECT
  
   value 
  
  =
  
   N.v.value(
  
  '
  
  .
  
  '
  
  , 
  
  '
  
  varchar(100)
  
  '
  
  ) 
  
  FROM
  
   A.
  
  [
  
  value
  
  ]
  
  .nodes(
  
  '
  
  /root/v
  
  '
  
  ) N(v)
) B


  
  --
  
  方法2.使用CTE完成
  
  

  
  ;
  
  with
  
   tt 
  
  as
  
   
(
  
  select
  
   id,
  
  [
  
  value
  
  ]
  
  =
  
  cast
  
  (
  
  left
  
  (
  
  [
  
  value
  
  ]
  
  ,
  
  charindex
  
  (
  
  '
  
  ,
  
  '
  
  ,
  
  [
  
  value
  
  ]
  
  +
  
  '
  
  ,
  
  '
  
  )
  
  -
  
  1
  
  ) 
  
  as
  
   
  
  nvarchar
  
  (
  
  100
  
  )),Split
  
  =
  
  cast
  
  (
  
  stuff
  
  (
  
  [
  
  value
  
  ]
  
  +
  
  '
  
  ,
  
  '
  
  ,
  
  1
  
  ,
  
  charindex
  
  (
  
  '
  
  ,
  
  '
  
  ,
  
  [
  
  value
  
  ]
  
  +
  
  '
  
  ,
  
  '
  
  ),
  
  ''
  
  ) 
  
  as
  
   
  
  nvarchar
  
  (
  
  100
  
  )) 
  
  from
  
   tb

  
  union
  
   
  
  all
  
  

  
  select
  
   id,
  
  [
  
  value
  
  ]
  
  =
  
  cast
  
  (
  
  left
  
  (Split,
  
  charindex
  
  (
  
  '
  
  ,
  
  '
  
  ,Split)
  
  -
  
  1
  
  ) 
  
  as
  
   
  
  nvarchar
  
  (
  
  100
  
  )),Split
  
  =
  
   
  
  cast
  
  (
  
  stuff
  
  (Split,
  
  1
  
  ,
  
  charindex
  
  (
  
  '
  
  ,
  
  '
  
  ,Split),
  
  ''
  
  ) 
  
  as
  
   
  
  nvarchar
  
  (
  
  100
  
  )) 
  
  from
  
   tt 
  
  where
  
   split
  
  >
  
  ''
  
  
)

  
  select
  
   id,
  
  [
  
  value
  
  ]
  
   
  
  from
  
   tt 
  
  order
  
   
  
  by
  
   id 
  
  option
  
   (MAXRECURSION 
  
  0
  
  )



  
  DROP
  
   
  
  TABLE
  
   tb


  
  /*
  
  
id          value
----------- ------------------------------
1           aa
1           bb
2           aaa
2           bbb
2           ccc

(5 行受影响)

  
  */