字符串变换为结果集

字符串转换为结果集？

SQL code

请教各位大大？如何将'01,02,03,04' 转换为1列的结果集01020304

SQL code

declare @s varchar(100)set @s='01,02,03,04'set @s=' select '''+replace(@s,',',''' as col union all select ''')+''''exec (@s)
[解决办法]
SQL code
USE testGODECLARE    @s NVARCHAR(MAX),@Sql NVARCHAR(MAX)SET @s='01,02,03,04'SET @Sql='Select '''+REPLACE(@s,',','''    Union all Select ''')+''''EXEC ( @Sql )
[解决办法]
SQL code
 ALTER function [dbo].[fnSys_SplitClass] (     ---字符串分割     @ClassSql varchar(max), --传入的字符串     @StrSeprate varchar(10)--分隔符 ) Returns @temp table(Class varchar(100)) --返回一个Table As  Begin     Declare @i int     Set @ClassSql =rtrim(ltrim(@ClassSql ))     Set @i=charindex(@StrSeprate,@ClassSql )     While @i>=1     Begin         Insert @temp values(left(@ClassSql ,@i-1))         Set @ClassSql =substring(@ClassSql ,@i+1,len(@ClassSql )-@i)         Set @i=charindex(@StrSeprate,@ClassSql )     End     If @ClassSql <>''          Insert @temp values(@ClassSql )     Return  End
[解决办法]
N种方法
SQL code
/* 拆分字符串   * */ --拆分单列+序号--方法一：利用数字辅助表if object_id('fn_SplitTSQL')is not null drop function  fn_SplitTSQLGOcreate FUNCTION dbo.fn_SplitTSQL  (@s NVARCHAR(MAX), @split NCHAR(1)) RETURNS TABLEASRETURN  SELECT    n - LEN(REPLACE(LEFT(array, n), @split, '')) + 1 AS rn,    SUBSTRING(array, n,      CHARINDEX(@split, array + @split, n) - n) AS col  FROM (SELECT @s AS array) AS D    JOIN dbo.Nums      ON n <= LEN(array)      AND SUBSTRING(@split + array, n, 1) = @split;GO--方法二：直接拆分if object_id('f_split')is not null drop function  f_splitgocreate function f_split(@s     varchar(8000),  --待分拆的字符串@split varchar(10)     --数据分隔符)returns tableas return (  select Row_number()over(order by Number) rn ,  substring(@s,number,charindex(@split,@s+@split,number)-number)as col  from master..spt_values  where type='p' and number<=len(@s+'a')   and charindex(@split,@split+@s,number)=number  )goselect * from dbo.f_split('11,2,3',',')select * from dbo.fn_SplitTSQL('11,2,3',',')GO--拆分多列if object_id('fn_MutiSplitTSQL')is not null drop function  fn_MutiSplitTSQLGOcreate FUNCTION dbo.fn_MutiSplitTSQL    (@s NVARCHAR(MAX), @split NCHAR(1) ,@Sub@split NCHAR(1)= N',') RETURNS TABLE  AS  RETURN  select * from (SELECT d.rn ,'col'+ cast( n - LEN(REPLACE(LEFT(col, n), @Sub@split, '')) + 1 as varchar(10)) AS attribute,      SUBSTRING(col, n, CHARINDEX(@Sub@split, col + @Sub@split, n) - n) AS value    FROM (  SELECT  n - LEN(REPLACE(LEFT(array, n), @split, '')) + 1 AS rn,      SUBSTRING(array, n, CHARINDEX(@split, array + @split, n) - n) AS col    FROM (SELECT @s AS array) AS D      JOIN dbo.Nums a       ON n <= LEN(array)       AND SUBSTRING(@split + array, n, 1) = @split ) AS D      JOIN dbo.Nums a       ON n <= LEN(col)       AND SUBSTRING(@Sub@split + col, n, 1) = @Sub@split ) as d    PIVOT(max(value) FOR attribute                         IN(col1,col2 ,col3,col4,col5 )) AS P  GO select col1,col2,col3,col4    from dbo.fn_MutiSplitTSQL    ('$092-1350,099201-080901,12050720,2012-6-11$092-0970,099204-072301,12050734,2012-6-11$','$',',')   GO