sqlserver结果集转为字符串
比如有语句select username from Employee 返回结果是： username 张三 李四 王五
怎么输出一个字符串类似： 张三，李四，王五 答：
declare @s varchar(1000)
select @s=isnull(@s+',','')+username from Employee select @s as username
--> 生成测试数据表:Employee
IF NOT OBJECT_ID('[Employee]') IS NULL DROP TABLE [Employee] GO
CREATE TABLE [Employee]([username] NVARCHAR(10)) INSERT [Employee]
SELECT N'张三' UNION ALL SELECT N'李四' UNION ALL SELECT N'王五' GO
--SELECT * FROM [Employee]
-->SQL查询如下:
DECLARE @S VARCHAR(100)
SELECT @S=ISNULL(@S+',','')+[username] FROM [Employee]
Zuolo： 上面的查询语句不能添加 order by进行排序，否则只能得到最后一条记录
PRINT @S --RESULT:
--张三,李四,王五 另外一个
请教各位大大？如何将
'01,02,03,04' 转换为1列的结果集 01 02 03 04
/* 拆分字符串 * */
--拆分单列+序号
--方法一：利用数字辅助表
if object_id('fn_SplitTSQL')is not null drop function fn_SplitTSQL GO
create FUNCTION dbo.fn_SplitTSQL
(@s NVARCHAR(MAX), @split NCHAR(1)) RETURNS TABLE AS RETURN 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_split go
create function f_split (
@s varchar(8000), --待分拆的字符串 @split varchar(10) --数据分隔符 )returns table
as
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 ) go
select * 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_MutiSplitTSQL GO
create 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
