database table field data segmentation problem __ Database

Source: Internet
Author: User
Tags id3

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)   
) as Begin while  
    (CHARINDEX (@split, @c) <> 0)   
            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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.