竖表变行表

[liangjuan01]

竖表变行表
--创建表
create table A(
工号 varchar(20),
日期 smalldatetime,
时间 varchar(20)
)
--插入测试数据
insert into A select '01', '2004-10-01', '07:50'
union select '01', '2004-10-01', '11:35'
union select '01', '2004-10-01', '14:20'
union select '01', '2004-10-02', '08:01'
union select '01', '2004-10-02', '14:30'
union select '02', '2004-10-01', '07:55'
union select '02', '2004-10-02', '07:58'
union select '03', '2004-10-01', '07:56'
--结果输出如下：
01 2004-10-01 07:50,11:35, 14:20
01 2004-10-02 08:01, 14:30
02 2004-10-01 07:55
02 2004-10-02 07:58
03 2004-10-01 07:56
请创建一个存储过程实现之。

[wyb0026]

http://community.csdn.net/Expert/topic/3881/3881318.xml?temp=.3074915

[xluzhong]

http://blog.csdn.net/xluzhong/articles/263039.aspx

[jinjazz]

create table A(
工号 varchar(20),
日期 smalldatetime,
时间 varchar(20)
)
--插入测试数据
insert into A select '01', '2004-10-01', '07:50'
union select '01', '2004-10-01', '11:35'
union select '01', '2004-10-01', '14:20'
union select '01', '2004-10-02', '08:01'
union select '01', '2004-10-02', '14:30'
union select '02', '2004-10-01', '07:55'
union select '02', '2004-10-02', '07:58'
union select '03', '2004-10-01', '07:56'

go
CREATE FUNCTION FunMergeCharField(@vchA varchar(10),@vchB varchar(10))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r=''
SELECT @r=@r+','+时间 FROM a WHERE 工号=@vchA and 日期=@vchB
RETURN(substring(@r,2,8000))
END
GO

select 工号,日期,dbo.FunMergeCharField(工号,日期) as 时间 from a
drop table A
drop FUNCTION FunMergeCharField
/*

工号 日期 时间
01 2004-10-01 00:00:00 07:50,11:35,14:20
01 2004-10-01 00:00:00 07:50,11:35,14:20
01 2004-10-01 00:00:00 07:50,11:35,14:20
01 2004-10-02 00:00:00 08:01,14:30
01 2004-10-02 00:00:00 08:01,14:30
02 2004-10-01 00:00:00 07:55
02 2004-10-02 00:00:00 07:58
03 2004-10-01 00:00:00 07:56
*/

[jinjazz]

--sorry，丢了分组，修改~~
create table A(
工号 varchar(20),
日期 smalldatetime,
时间 varchar(20)
)
--插入测试数据
insert into A select '01', '2004-10-01', '07:50'
union select '01', '2004-10-01', '11:35'
union select '01', '2004-10-01', '14:20'
union select '01', '2004-10-02', '08:01'
union select '01', '2004-10-02', '14:30'
union select '02', '2004-10-01', '07:55'
union select '02', '2004-10-02', '07:58'
union select '03', '2004-10-01', '07:56'

go
CREATE FUNCTION FunMergeCharField(@vchA varchar(10),@vchB varchar(10))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r=''
SELECT @r=@r+','+时间 FROM a WHERE 工号=@vchA and 日期=@vchB
RETURN(substring(@r,2,8000))
END
GO

select 工号,日期,dbo.FunMergeCharField(工号,日期) as 时间 from a group by 工号,日期
drop table A
drop FUNCTION FunMergeCharField
/*

工号 日期 时间
01 2004-10-01 00:00:00 07:50,11:35,14:20
01 2004-10-02 00:00:00 08:01,14:30
02 2004-10-01 00:00:00 07:55
02 2004-10-02 00:00:00 07:58
03 2004-10-01 00:00:00 07:56
*/