表 A
date1 time1
2010-10-20 12:00
2010-10-20 13:00
2010-10-21 14:00
2010-10-21 15:00
2010-10-20 16:00希望结果:date1 time1
2010-10-20 12:00;13:00;16:00
2010-10-21 14:00;15:00
是sql2000数据库
date1 time1
2010-10-20 12:00
2010-10-20 13:00
2010-10-21 14:00
2010-10-21 15:00
2010-10-20 16:00希望结果:date1 time1
2010-10-20 12:00;13:00;16:00
2010-10-21 14:00;15:00
是sql2000数据库
解决方案 »
- 存储过程问题
- 如何把T_Temp_V_AnalyseProjectMain变为T_Temp_@User_V_AnalyseProjectMain
- SQL 2000出现以下的错误:MMC 不能打开文件 C:\Program Files\Microsoft SQL Server\80\Tools\BINN\SQL Server Enterprise Manager.MSC.
- 在线等待,请问除了临时表以外,有没有什么方法能够通过存储过程传递多给记录集给应用程序
- 关于替换的问题
- SQL 数据表重新创建后,表约束,触发器的创建
- 触发器的问题!
- 子陌红尘《《感谢钻钻的答案!不过现在还是不对滴!有钻的帖子要加分滴!
- 十万火急!!!问过很多人了 !!数据库太大的问题!!!跪求高人解答!!!
- 邹建请进!关于数据库中存/取文件
- 关于SQL 2008链接服务器的问题
- 如何把SQLServer2008数据库文件转成支持SQLServer2005的
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:[TB]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]')
AND type in (N'U'))
DROP TABLE [TB]
GO---->建表
create table [TB](date1 varchar(10),time1 varchar(5))
insert [TB]
select '2010-10-20','12:00' union all
select '2010-10-20','13:00' union all
select '2010-10-21','14:00' union all
select '2010-10-21','15:00' union all
select '2010-10-20','16:00'
GO--> 查询结果
SELECT *
FROM [TB]SELECT a.date1,
status =stuff((
select ','+convert(varchar(20),time1) FROM [TB] WHERE date1 = a.date1 for xml path(''))
,1,1,'')
FROM [TB] a
group by a.date1SELECT a.date1,
status =STUFF(REPLACE(REPLACE(
(select time1
FROM [TB]
where date1 = a.date1
FOR XML AUTO
), '<TB time1="', ','), '"/>', ''), 1, 1, '')
FROM [TB] a
group by a.date1 --> 删除表格
--DROP TABLE [TB]
returns varchar(1000)
as
begin
declare @s varchar(1000)
set @s=''
select @s=@s+isnull(time1,'')+';'
from A where order_no=@date1
return (@s)
endselect date1,dbo.fn_date(date1) as time1
from A
group by date1
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:[TB]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]')
AND type in (N'U'))
DROP TABLE [TB]
GO---->建表
create table [TB]([name] varchar(4),[status] varchar(8))
insert [TB]
select '小张','普通员工' union all
select '小张','组长' union all
select '小二','经理' union all
select '小三','老板' union all
select '小三','员工'
GO方法一:
SELECT a.name,
status =stuff((
select ','+convert(varchar(20),status) FROM [TB] WHERE name = a.name for xml path(''))
,1,1,'')
FROM [TB] a
group by a.name方法二:
SELECT a.name,
status =STUFF(REPLACE(REPLACE(
(select status
FROM [TB]
where name = a.name
FOR XML AUTO
), '<TB status="', ','), '"/>', ''), 1, 1, '')
FROM [TB] a
group by a.name
--> 查询结果
SELECT * FROM [TB]
--> 删除表格
--DROP TABLE [TB]
returns varchar(1000)
as
begin
declare @s varchar(1000)
set @s=''
select @s=@s+isnull(time1,'')+';'
from A where date1=@date1
return (@s)
end
select date1,dbo.fn_date(date1) as time1
from A
group by date1----------------
2010-10-20 12:00;13:00;16:00;
2010-10-21 14:00;15:00;
所以1L不行的for xml path错误的
2L,新建 一个函数 ,正解!
这里引用2L的语句
CREATE function dbo.fn_date(@date1 as varchar(50))
returns varchar(1000)
as
begin
declare @s varchar(1000)
set @s=''
select @s=@s+isnull(time1,'')+';'
from A where date1=@date1
return (@s)
end
select date1,dbo.fn_date(date1) as time1
from A
group by date1----------------
2010-10-20 12:00;13:00;16:00;
2010-10-21 14:00;15:00;
CREATE function fn_date(@date datetime)
returns varchar(1000)
as
begin
declare @result varchar(1000)
set @result=''
select @result=@result+';'+isnull(time1,'') from A where date1=@date
select @result=stuff(@result,1,1,'')
return @result
endselect date1,dbo.fn_date(date1) as time1
from A