Data Segmentation of database table field

Sometimes in order to reduce the number of records stored, multiple records may be merged into one display. The occurrence of this situation is mainly reflected in the other fields of the table are the same, only one field is the case of change, for example, in personnel management, the ID of the relevant person in the Department is placed in a field of a record, separated by commas. The need now is to split a record into multiple records by ID field.

CREATE TABLE [dbo]. [Table_dept] ([Dept_code] [int] null,[content] [nvarchar] (+) null,[a0188s] [nvarchar] (max) NULL) On [Primary]goinsert to table_dept Select 1000, ' General Affairs Department ', ' 350,688,258 ' union ALL Select 1001, ' General manager's Room ', ' 2,3,4,298 '

Query the table results as follows, where Dept_code department code, content is the department name, A0188S is the relevant person.

Now you need to break the IDs in a0188s into multiple displays. Consider the use of a custom string segmentation 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

However, the function can only handle a single record, where a cursor is considered to traverse the original table, decomposed and then stored in a temporary table.

IF object_id (' tempdb.. #TEMPTB1 ') is not nullbegindrop table #TEMPTB1ENDCREATE table #TEMPTB1 ([dept_code] [int] NULL, [content] [nvarchar] () null, [a0188s] [nvarchar] (max) null) IF object_id (' tempdb. #TEMPTB2 ') is not nullbegindrop table #TEMPTB2ENDCREATE table #TEMPTB2 ([PID] [nvarchar] (max) NULL) declare @DEPT_CODE i Ntdeclare @content varchar (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, @id3 while (@ @fetch_status = 0) beginset @DEPT_CODE =convert (int, @id1) set @content =convert (varchar (), @id2) Set @A0188s = CONVERT (varchar (max), @id3) TRUNCATE TABLE #TEMPTB2 INSERT INTO #TEMPTB2 select * from Split (@A0188s, ', ') insert int o #TEMPTB1 Select @DEPT_CODE, @content, PID from #TEMPTB2fetch next to My_cursor1 into @id1, @id2, @id3endclose my_cursor1d Eallocate My_cursor1select * from #TEMPTB1

Get the final result

Data Segmentation of database table field