Database Table field data segmentation

Sometimes, to reduce the number of stored records, multiple records may be merged into one display. In this case, the other fields recorded in the table are the same, and only one field is changed. For example, in personnel management, the IDS of related persons in the Department are placed in a field of a record, separated by commas. The current requirement is

Sometimes, to reduce the number of stored records, multiple records may be merged into one display. In this case, the other fields recorded in the table are the same, and only one field is changed. For example, in personnel management, the IDS of related persons in the Department are placed in a field of a record, separated by commas. The current requirement is

Sometimes, to reduce the number of stored records, multiple records may be merged into one display. In this case, the other fields recorded in the table are the same, and only one field is changed. For example, in personnel management, the IDS of related persons in the Department are placed in a field of a record, separated by commas. The requirement is to split a record into multiple records by ID field.

Create table [dbo]. [Table_Dept] ([DEPT_CODE] [int] NULL, [content] [nvarchar] (50) NULL, [A0188s] [nvarchar] (max) NULL) ON [PRIMARY] GOinsert into Table_Dept select 1000, 'General Department ', '000000' union all select 350,688,258, 'general manager', '2, 3, 4, 298 'query the result of this table, where DEPT_CODE department code, content is the department name, A0188s is the relevant person.

Now you need to separate the IDs in A0188s into multiple display entries. Use a custom string segmentation function. The segmentation function script is as follows:

CREATE   FUNCTION [dbo].[Split]   (   @c VARCHAR(MAX) ,   @split VARCHAR(50)   )   RETURNS @t TABLE ( col VARCHAR(50) )   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( col ) VALUES  ( @c )       RETURN  END

However, this function can only process a single record. Here, we consider traversing the original table with a cursor, decomposing them one by one, and then storing them in the temporary table.

IF object_id('tempdb..#TEMPTB1') is not nullBEGINdrop table #TEMPTB1ENDCREATE table #TEMPTB1(    [DEPT_CODE] [int] NULL,    [content] [nvarchar](50) NULL,    [A0188s] [nvarchar](max) NULL)IF object_id('tempdb..#TEMPTB2') is not nullBEGINdrop table #TEMPTB2ENDCREATE table #TEMPTB2(    [pid] [nvarchar](max) NULL)declare @DEPT_CODE intdeclare @content varchar(50)declare @A0188s varchar(max)exec('declare my_cursor1 cursor for select * from [Table_Dept]')open my_cursor1declare @id1 sysnamedeclare @id2 sysnamedeclare @id3 sysnamefetch next from my_cursor1 into @id1,@id2,@id3while(@@fetch_status= 0)beginset @DEPT_CODE =convert(int,@id1)set @content =convert(varchar(50),@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 from #TEMPTB2fetch next from my_cursor1 into @id1,@id2,@id3endclose my_cursor1deallocate my_cursor1select * from #TEMPTB1

Get the final result