SQL字符串分割转列，Sql列转字符串

declare @strVar varchar(2000)
declare @ResultVar varchar(2000)
set @ResultVar='[薄饼],[点心],[海鲜],[酒吧],[楼面],[茗茶],[乳鸽],[烧味],[生蚝],[套餐宴席],[煨汤],[中厨]'
select * from Sys_Fn_Get_TableFromStringSplit(@strVar,',')

--Result

Id　　FlowId

[薄饼]   1
[点心]   2
[海鲜]   3
[酒吧]   4
[楼面]   5
[茗茶]   6
[乳鸽]   7
[烧味]   8
[生蚝]   9
[套餐宴席]   10
[煨汤]   11
[中厨]   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([薄饼]) as [薄饼],sum([点心]) as [点心],sum([海鲜]) as [海鲜],sum([酒吧]) as [酒吧],sum([楼面]) as [楼面]

--字符串分割成表函数

create function [dbo].[Sys_Fn_Get_TableFromStringSplit]
(
   @Strings varchar(max),     --要分割成表的字符串盘点时候要分割的数据可能超过8000
   @Spliter varchar(10)        --分隔符
)
returns @Table table(Id varchar(50),FlowId int identity(1,1))
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) -@index);
            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