sqlserver 中 怎样把一个字段的多条内容在一个字段中显示
发布网友
共1个回答
热心网友
使用自定义函数
--测试表
create table Test
(F1 varchar(10),
F2 datetime,
F3 datetime)
--插入数据
insert into Test
select '张伟','14:20:00','18:00:00'
union
select '张三','9:00:00','18:00:00'
union
select '李四','9:00:00','18:00:00'
union
select '王五','9:00:00','18:00:00'
union
select '陈六','9:00:00','18:00:00'
union
select '小刘','10:00:00','18:00:00'
union
select '张德','10:00:00','18:00:00'
--一条动态SQL语句
go
--合并函数
CREATE FUNCTION MergeCharField(@t1 datetime,@t2 datetime)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r=''
SELECT @r=@r+','+rtrim(F1) FROM Test WHERE F2=@t1 and f3=@t2
RETURN(substring(@r,2,8000))
END
GO
--调用
select dbo.MergeCharField(F2,f3) name,CONVERT(varchar(12),f2,114) time1,CONVERT(varchar(12),f3,114) time2 from test group by F2,f3
效果图：
