合并拆分表

SQL code

  
  

   
   
   
   --
   
   合并分拆表
   
   
/*
   
   *****************************************************************************************************************************************************
合并分拆表数据

整理人：中国风(Roy)

日期:2008.06.06
*****************************************************************************************************************************************************
   
   */
   
   


   
   --
   
   > --> (Roy)生成測試數據
   
   

   
    

   
   if
   
    
   
   not
   
    
   
   object_id
   
   (
   
   '
   
   Tab
   
   '
   
   ) 
   
   is
   
    
   
   null
   
   
    
   
   drop
   
    
   
   table
   
    Tab

   
   Go
   
   

   
   Create
   
    
   
   table
   
    Tab(
   
   [
   
   Col1
   
   ]
   
    
   
   int
   
   ,
   
   [
   
   Col2
   
   ]
   
    
   
   nvarchar
   
   (
   
   1
   
   ))

   
   Insert
   
    Tab

   
   select
   
    
   
   1
   
   ,N
   
   '
   
   a
   
   '
   
    
   
   union
   
    
   
   all
   
   

   
   select
   
    
   
   1
   
   ,N
   
   '
   
   b
   
   '
   
    
   
   union
   
    
   
   all
   
   

   
   select
   
    
   
   1
   
   ,N
   
   '
   
   c
   
   '
   
    
   
   union
   
    
   
   all
   
   

   
   select
   
    
   
   2
   
   ,N
   
   '
   
   d
   
   '
   
    
   
   union
   
    
   
   all
   
   

   
   select
   
    
   
   2
   
   ,N
   
   '
   
   e
   
   '
   
    
   
   union
   
    
   
   all
   
   

   
   select
   
    
   
   3
   
   ,N
   
   '
   
   f
   
   '
   
   

   
   Go
   
   

合并表:

SQL2000用函数:


   
   go
   
   

   
   if
   
    
   
   object_id
   
   (
   
   '
   
   F_Str
   
   '
   
   ) 
   
   is
   
    
   
   not
   
    
   
   null
   
   
    
   
   drop
   
    
   
   function
   
    F_Str

   
   go
   
   

   
   create
   
    
   
   function
   
    F_Str(
   
   @Col1
   
    
   
   int
   
   )

   
   returns
   
    
   
   nvarchar
   
   (
   
   100
   
   )

   
   as
   
   

   
   begin
   
   
    
   
   declare
   
    
   
   @S
   
    
   
   nvarchar
   
   (
   
   100
   
   )
    
   
   select
   
    
   
   @S
   
   =
   
   isnull
   
   (
   
   @S
   
   +
   
   '
   
   ,
   
   '
   
   ,
   
   ''
   
   )
   
   +
   
   Col2 
   
   from
   
    Tab 
   
   where
   
    Col1
   
   =
   
   @Col1
   
   
    
   
   return
   
    
   
   @S
   
   

   
   end
   
   

   
   go
   
   

   
   Select
   
    
   
   distinct
   
    Col1,Col2
   
   =
   
   dbo.F_Str(Col1) 
   
   from
   
    Tab


   
   go
   
   

SQL2005用XML:

方法1:


   
   select
   
    
    a.Col1,Col2
   
   =
   
   stuff
   
   (b.Col2.value(
   
   '
   
   /R[1]
   
   '
   
   ,
   
   '
   
   nvarchar(max)
   
   '
   
   ),
   
   1
   
   ,
   
   1
   
   ,
   
   ''
   
   )

   
   from
   
    
    (
   
   select
   
    
   
   distinct
   
    COl1 
   
   from
   
    Tab) a

   
   Cross
   
    apply
    (
   
   select
   
    COl2
   
   =
   
   (
   
   select
   
    N
   
   '
   
   ,
   
   '
   
   +
   
   Col2 
   
   from
   
    Tab 
   
   where
   
    Col1
   
   =
   
   a.COl1 
   
   For
   
    XML PATH(
   
   ''
   
   ), ROOT(
   
   '
   
   R
   
   '
   
   ), TYPE))b

方法2:


   
   select
   
    
    a.Col1,COl2
   
   =
   
   replace
   
   (b.Col2.value(
   
   '
   
   /Tab[1]
   
   '
   
   ,
   
   '
   
   nvarchar(max)
   
   '
   
   ),
   
   char
   
   (
   
   44
   
   )
   
   +
   
   char
   
   (
   
   32
   
   ),
   
   char
   
   (
   
   44
   
   ))

   
   from
   
    
    (
   
   select
   
    
   
   distinct
   
    COl1 
   
   from
   
    Tab) a

   
   cross
   
    apply
    (
   
   select
   
    Col2
   
   =
   
   (
   
   select
   
    COl2 
   
   from
   
    Tab  
   
   where
   
    COl1
   
   =
   
   a.COl1 
   
   FOR
   
    XML AUTO, TYPE)
                .query(
   
   '
   
   <Tab>
                {for $i in /Tab[position()<last()]/@COl2 return concat(string($i),",")}
                {concat("",string(/Tab[last()]/@COl2))}
                </Tab>
   
   '
   
   )
                )b

SQL05用CTE:

;
   
   with
   
    roy 
   
   as
   
   (
   
   select
   
    Col1,Col2,row
   
   =
   
   row_number()
   
   over
   
   (partition 
   
   by
   
    COl1 
   
   order
   
    
   
   by
   
    COl1) 
   
   from
   
    Tab)
,Roy2 
   
   as
   
   
(
   
   select
   
    COl1,
   
   cast
   
   (COl2 
   
   as
   
    
   
   nvarchar
   
   (
   
   100
   
   ))COl2,row 
   
   from
   
    Roy 
   
   where
   
    row
   
   =
   
   1
   
    

   
   union
   
    
   
   all
   
    

   
   select
   
    a.Col1,
   
   cast
   
   (b.COl2
   
   +
   
   '
   
   ,
   
   '
   
   +
   
   a.COl2 
   
   as
   
    
   
   nvarchar
   
   (
   
   100
   
   )),a.row 
   
   from
   
    Roy a 
   
   join
   
    Roy2 b 
   
   on
   
    a.COl1
   
   =
   
   b.COl1 
   
   and
   
    a.row
   
   =
   
   b.row
   
   +
   
   1
   
   )

   
   select
   
    Col1,Col2 
   
   from
   
    Roy2 a 
   
   where
   
    row
   
   =
   
   (
   
   select
   
    
   
   max
   
   (row) 
   
   from
   
    roy 
   
   where
   
    Col1
   
   =
   
   a.COl1) 
   
   order
   
    
   
   by
   
    Col1 
   
   option
   
    (MAXRECURSION 
   
   0
   
   )


生成结果:

   
   /*
   
   
Col1        COl2
----------- ------------
1           a,b,c
2           d,e
3           f

(3 行受影响)

   
   */
   
   


拆分表:


   
   --
   
   > --> (Roy)生成測試數據
   
   

   
    

   
   if
   
    
   
   not
   
    
   
   object_id
   
   (
   
   '
   
   Tab
   
   '
   
   ) 
   
   is
   
    
   
   null
   
   
    
   
   drop
   
    
   
   table
   
    Tab

   
   Go
   
   

   
   Create
   
    
   
   table
   
    Tab(
   
   [
   
   Col1
   
   ]
   
    
   
   int
   
   ,
   
   [
   
   COl2
   
   ]
   
    
   
   nvarchar
   
   (
   
   5
   
   ))

   
   Insert
   
    Tab

   
   select
   
    
   
   1
   
   ,N
   
   '
   
   a,b,c
   
   '
   
    
   
   union
   
    
   
   all
   
   

   
   select
   
    
   
   2
   
   ,N
   
   '
   
   d,e
   
   '
   
    
   
   union
   
    
   
   all
   
   

   
   select
   
    
   
   3
   
   ,N
   
   '
   
   f
   
   '
   
   

   
   Go
   
   

SQL2000用辅助表:

   
   if
   
    
   
   object_id
   
   (
   
   '
   
   Tempdb..#Num
   
   '
   
   ) 
   
   is
   
    
   
   not
   
    
   
   null
   
   
    
   
   drop
   
    
   
   table
   
    #Num

   
   go
   
   

   
   select
   
    
   
   top
   
    
   
   100
   
    ID
   
   =
   
   Identity
   
   (
   
   int
   
   ,
   
   1
   
   ,
   
   1
   
   ) 
   
   into
   
    #Num 
   
   from
   
    syscolumns a,syscolumns b

   
   Select
   
    
    a.Col1,COl2
   
   =
   
   substring
   
   (a.Col2,b.ID,
   
   charindex
   
   (
   
   '
   
   ,
   
   '
   
   ,a.Col2
   
   +
   
   '
   
   ,
   
   '
   
   ,b.ID)
   
   -
   
   b.ID) 

   
   from
   
    
    Tab a,#Num b

   
   where
   
   
    
   
   charindex
   
   (
   
   '
   
   ,
   
   '
   
   ,
   
   '
   
   ,
   
   '
   
   +
   
   a.Col2,b.ID)
   
   =
   
   b.ID 
   
   --
   
   也可用 substring(','+a.COl2,b.ID,1)=','
   
   

   
   

SQL2005用Xml:


   
   select
   
    
    a.COl1,b.Col2

   
   from
   
    
    (
   
   select
   
    Col1,COl2
   
   =
   
   convert
   
   (xml,
   
   '
   
   <root><v>
   
   '
   
   +
   
   replace
   
   (COl2,
   
   '
   
   ,
   
   '
   
   ,
   
   '
   
   </v><v>
   
   '
   
   )
   
   +
   
   '
   
   </v></root>
   
   '
   
   ) 
   
   from
   
    Tab)a

   
   outer
   
    apply
    (
   
   select
   
    Col2
   
   =
   
   C.v.value(
   
   '
   
   .
   
   '
   
   ,
   
   '
   
   nvarchar(100)
   
   '
   
   ) 
   
   from
   
    a.COl2.nodes(
   
   '
   
   /root/v
   
   '
   
   )C(v))b




SQL05用CTE:

;
   
   with
   
    roy 
   
   as
   
    
(
   
   select
   
    Col1,COl2
   
   =
   
   cast
   
   (
   
   left
   
   (Col2,
   
   charindex
   
   (
   
   '
   
   ,
   
   '
   
   ,Col2
   
   +
   
   '
   
   ,
   
   '
   
   )
   
   -
   
   1
   
   ) 
   
   as
   
    
   
   nvarchar
   
   (
   
   100
   
   )),Split
   
   =
   
   cast
   
   (
   
   stuff
   
   (COl2
   
   +
   
   '
   
   ,
   
   '
   
   ,
   
   1
   
   ,
   
   charindex
   
   (
   
   '
   
   ,
   
   '
   
   ,Col2
   
   +
   
   '
   
   ,
   
   '
   
   ),
   
   ''
   
   ) 
   
   as
   
    
   
   nvarchar
   
   (
   
   100
   
   )) 
   
   from
   
    Tab

   
   union
   
    
   
   all
   
   

   
   select
   
    Col1,COl2
   
   =
   
   cast
   
   (
   
   left
   
   (Split,
   
   charindex
   
   (
   
   '
   
   ,
   
   '
   
   ,Split)
   
   -
   
   1
   
   ) 
   
   as
   
    
   
   nvarchar
   
   (
   
   100
   
   )),Split
   
   =
   
    
   
   cast
   
   (
   
   stuff
   
   (Split,
   
   1
   
   ,
   
   charindex
   
   (
   
   '
   
   ,
   
   '
   
   ,Split),
   
   ''
   
   ) 
   
   as
   
    
   
   nvarchar
   
   (
   
   100
   
   )) 
   
   from
   
    Roy 
   
   where
   
    split
   
   >
   
   ''
   
   
)

   
   select
   
    COl1,COl2 
   
   from
   
    roy 
   
   order
   
    
   
   by
   
    COl1 
   
   option
   
    (MAXRECURSION 
   
   0
   
   )

生成结果:

   
   /*
   
   
Col1        COl2
----------- -----
1           a
1           b
1           c
2           d
2           e
3           f

   
   */