Shulou(Shulou.com)06/01 Report--

Use a string to describe the contents of a table and use this table-valued function to restore it to a table if needed.

Because it is impossible to predict the number of columns and column names of the table, [column conversion] is performed when needed.

This function, like the [StrSplit] function, can also decompose a string into a single column, as long as the argument @ ColumnStr=''

Of course, you can also store the column name of the table in a string.

Here is the SQL code:

FUNCTION [dbo]. [StrSplit_Tab] (

@ String nvarchar (MAX)-- string to be split

, @ RowStr nvarchar (10)-line separator

, @ ColumnStr nvarchar (10)-column separator

, @ NullItem int=0-- whether to keep the location number of [empty element] [item]. Not reserved for 0, not reserved for 0.

) returns @ Tab table (

[Row] int-- [Row]: line number (1, 2, 3, 4, 5.)

, [Column] nvarchar (20)-- [Column]: column number (F1 Magi F2 Magi F3.)

, [item] nvarchar (MAX)-- [item]: cell element

As

Begin

IF ISNULL (@ RowStr,'') =''begin-- No [line separator @ RowStr], return the original text

Insert into @ Tab ([Row], [Column], [item]) VALUES (1)

RETURN

End

SET @ NullItem=ISNULL (@ NullItem,0)

Declare @ Ritem nvarchar (MAX);-- text

Declare @ begin as int=1,@end as int;-- intercepts the text, starting and ending position

Declare @ RL AS int=LEN (@ RowStr);-- Separator length

Declare @ R AS int=0;-- Line number

-- decompose rows

Set @ end=charindex (@ RowStr,@String,@begin)

While (@ end0)

Begin

Set @ Ritem = substring (@ String,@begin,@end-@begin)

IF @ NullItem0 SET @ R=@R+1

IF len (@ Ritem) > 0 begin

-- decompose column-- self-call

IF @ NullItem=0 SET @ R=@R+1

Insert into @ Tab ([Row], [Column], [item])

SELECT @ R (Row) as nvarchar (50), [item]

FROM [dbo]. [StrSplit_Tab] (@ Ritem,@ColumnStr,'')

End

Set @ begin=@end+@RL

Set @ end=charindex (@ RowStr,@String,@begin)

End

-- end text processing

Set @ Ritem = substring (@ String,@begin,len (@ String) + 1-@begin)

IF len (@ Ritem) > 0 begin

SET @ R=@R+1

-- decompose column-- self-call

Insert into @ Tab ([Row], [Column], [item])

SELECT @ R (Row) as nvarchar (50), [item]

FROM [dbo]. [StrSplit_Tab] (@ Ritem,@ColumnStr,'')

End

Return

End

-- = use example =

Select [Row] as' Row'

Max ([F1]) as' F1'

Max ([F2]) as' F2'

Max ([F3]) as' F3'

From [dbo]. [StrSplit_Tab] (

: well-off: 342222199506186034; Yang Lan: 342222197909226046; Xiang Feng: 42282519890310110X; Yang Yu: 5102271975102765Ten

,';'

,':'

, 1)

Pivot (

Max ([item]) for [Column]

In (F1, F2, F3)

) as B

Group by [Row]

Order by [Row] asc

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.