SQL converts ' 1,2,3,4 ' to the corresponding table, the field separates the table-valued function, and the line becomes more rows,

Redesign the table when we often encounter such a situation, the value of the field is 1,2,3,4 and other characters separated, the existence of a number, but the display is the corresponding category, direct, clear,

How to get it done in two steps, as follows:

The first step is to create a table-valued function that turns 1,2,3,4 into

The function is found from the Internet, the code is as follows:

Create FUNCTION [dbo]. [Fn_split] (
@String nvarchar (max),
@Delimiter nvarchar (10)
)
RETURNS @ValueTable TABLE ([Value] NVARCHAR (max), [id] int)
BEGIN
DECLARE @NextString nvarchar (max),
@Pos int,
@NextPos int,
@CommaCheck nvarchar (1),
@id int

Set @id =1

SET @NextString = ' '
SET @CommaCheck = Right (@String, 1)

SET @String = @String + @Delimiter

SET @Pos = CHARINDEX (@Delimiter, @String)
SET @NextPos = 1

while (@pos <> 0)
BEGIN
SET @NextString = SUBSTRING (@String, 1, @Pos-1)

INSERT into @ValueTable ([Value],[id]) VALUES (@NextString, @id)

SET @String = SUBSTRING (@String, @pos +1,len (@String))

SET @NextPos = @Pos
SET @pos = CHARINDEX (@Delimiter, @String)

Set @id = @id +1
END

RETURN
END


GO

The second step is to create a scalar function, this step is more important, because these scalar functions are not often used, the total feeling can be realized is not thinking good idea, that is, the line of the row,

The code is as follows: ALTER FUNCTION [dbo]. [Getbiontionname] (@nodeId varchar (64))
RETURNS varchar (64)
As

BEGIN
DECLARE @name varchar (64)
Set @name = (select Name+ ', ' from Gwt_biont where cast (id as varchar) in (select CAST (value as Int.) from Dbo.fn_split (@node Id, ', ')) for XML Path (")")
return substring (@name, 1,len (@name)-1)

END

GO

That's how it's done.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

SQL converts ' 1,2,3,4 ' to the corresponding table, the field separates the table-valued function, and the line becomes more rows,