database table field data segmentation problem __ Database

Sometimes, to reduce the number of records stored, multiple records may be merged into one display. This situation is mainly reflected in the record in the other fields of the table are the same, only a certain field is a change in this case, such as personnel Management, the Department of the relevant person's ID is placed in a field in a record, separated by commas. The requirement now is to split a record into multiple records by ID field.

CREATE TABLE [dbo]. [Table_dept] (
	[Dept_code] [int] null,
	[content] [nvarchar] (m) null,
	[a0188s] [nvarchar] (max) null
) on [primary]< C4/>go

INSERT INTO table_dept Select 1000, ' General Department ', ' 350,688,258 ' union ALL Select 1001, ' General manager's Room ', ' 2,3,4,298 '

Query the results of the table below, which Dept_code department code, content is the department name, A0188S is the relevant person.

Now you need to decompose the IDs in a0188s into multiple displays. Consider a custom string partitioning function implementation, Split function script:

CREATE   FUNCTION [dbo].[ Split]   
(   
@c VARCHAR (MAX),   
@split VARCHAR)   
RETURNS @t TABLE (col VARCHAR  
) as Begin while  
    (CHARINDEX (@split, @c) <> 0)   
        begin  
            INSERT  @t (col)   
            VALUES  (SUBSTRING ( @c, 1, CHARINDEX (@split, @c)-1))   
            SET @c = STUFF (@c, 1, CHARINDEX (@split, @c), ') End  
    INSERT  @t (CO L) VALUES  (@c) return end  

But the function can only handle a single record, here consider using a cursor to traverse the original table, decomposed and then stored in a temporary table.

IF object_id (' tempdb.. #TEMPTB1 ') is isn't null BEGIN drop table #TEMPTB1 end CREATE table #TEMPTB1 ([dept_code] [int] NULL, [content] [n VARCHAR] () NULL, [a0188s] [nvarchar] (max) null) IF object_id (' tempdb ... #TEMPTB2 ') is not null BEGIN drop table #TEMPTB2 end CREATE table #TEMPTB2 ([PID] [nvarchar] (max) null) declare @D Ept_code int DECLARE @content varchar (m) DECLARE @A0188s varchar (max) EXEC (' Declare my_cursor1 for SELECT * FROM [ Table_dept] ' open my_cursor1 declare @id1 sysname declare @id2 sysname declare @id3 sysname fetch NEXT from My_cursor1 in To @id1, @id2, @id3 while (@ @fetch_status = 0) begin set @DEPT_CODE =convert (int, @id1) set @content =convert (varchar  (m), @id2) set @A0188s =convert (varchar (max), @id3) TRUNCATE TABLE #TEMPTB2 INSERT INTO #TEMPTB2 select * FROM  Split (@A0188s, ', ') insert into #TEMPTB1 select @DEPT_CODE, @content, PID out #TEMPTB2 fetch next from My_cursor1 Into the @id1, @id2, @id3 end Close My_cursor1 deAllocate My_cursor1 SELECT * from #TEMPTB1 

Get the final result