SQL string split to column, SQL column to string

DECLARE @strVar varchar (2000)
DECLARE @ResultVar varchar (2000)
Set @ResultVar = ' [Pancake],[Dim sum],[Seafood],[bar],[floor],[tea],[pigeon],[roasted],[oyster],[Set banquet],[Simmer soup],[Chinese chef] '
SELECT * from Sys_fn_get_tablefromstringsplit (@strVar, ', ')

--result

Id Flowid

[Pizza] 1
[Dim sum] 2
[Seafood] 3
[Bar] 4
[Floor Area] 5
[Tea] 6
[Pigeon] 7
[Burning Flavor] 8
[Raw Oyster] 9
[Package Banquet] 10
[Simmer soup] 11
[Chinese Kitchen] 12

--diy
Set @ResultVar =stuff ((select ', ' + ' sum (' + p.id+ ') as ' +p.id
From Sys_fn_get_tablefromstringsplit (@strVar, ', ') p for XML Path (")), 1, 1, ')

Select @ResultVar

--result

SUM ([pizza]) as [Pizza],sum ([dim sum]) As [Dim Sum],sum ([seafood]) as [Seafood],sum ([bar]) as [Bar],sum ([floor]) as [floor area]

--string splitting into a table function

Create function [dbo]. [Sys_fn_get_tablefromstringsplit]
(
@Strings varchar (max)--the string to be split into a table may be divided by more than 8000 data
@Spliter varchar (10)--delimiter
)
Returns @Table Table (Id varchar (), Flowid int identity (+))
As
Begin
declare @index int, @tempValue varchar (50)

while (1 = 1)
Begin
Set @index = charindex (@Spliter, @Strings);
if (@index > 0)
Begin
Set @tempValue = substring (@Strings, 1, @index);
Set @Strings = substring (@Strings, @index + 1, len (@Strings) [email protected]);
Set @tempValue = replace (LTrim (RTrim (@tempValue)), @Spliter, ");
if (len (@tempValue) > 0)
Begin
INSERT INTO @Table
Values
(
@tempValue
);
End
End
Else
Begin
Set @tempValue = LTrim (RTrim (@Strings));
if (len (@tempValue) > 0)
Begin
INSERT INTO @Table
Values
(
@tempValue
);
End
Break
End
End
Return
/*********************************demo*****************************************

SELECT * FROM dbo. Sys_fn_get_tablefromstringsplit (' 1,2,3,,4,5,6 ', ', ');
SELECT * FROM dbo. Sys_fn_get_tablefromstringsplit (' 1 2 3,,4 5,6 ', ');
SELECT * FROM dbo. Sys_fn_get_tablefromstringsplit (' 1,2;3,,4,5;6 ', '; ');

*******************************************************************************/
End

SQL string split to column, SQL column to string