usertask表有很多类似上面的数据,是动态值 “动态值”的话!参考如下:原地址:http://topic.csdn.net/u/20100423/17/914d517d-a19e-4c75-bbf6-950188d80d58.html--------------------SQL Server数据格式化工具------------------- --------------------------------------------------------------- -- DESIGNER :happycell188(喜喜) -- QQ :584738179 -- Development Tool :Microsoft Visual C++ 6.0 C Language -- FUNCTION :CONVERT DATA TO T-SQL --------------------------------------------------------------- -- Microsoft SQL Server 2005 -- Developer Edition on Microsoft Windows XP [版本 5.1.2600] --------------------------------------------------------------- ---------------------------------------------------------------use test go if object_id('test.dbo.tb') is not null drop table tb -- 创建数据表 create table tb ( 时间 char(11), 员工 char(7), 班别 char(5) ) go --插入测试数据 insert into tb select '2010-04-23','王梅','早班' union all select '2010-04-25','周晓丽','晚班' union all select '2010-04-25','刘燕','晚班' union all select '2010-04-25','王梅','晚班' union all select '2010-04-23','明月','早班' union all select '2010-04-24','陈红云','早班' union all select '2010-04-24','孙红梅','早班' union all select '2010-04-24','刘燕','早班' union all select '2010-04-23','孙红梅','早班' union all select '2010-04-23','陈红云','早班' go --代码实现declare @sql varchar(1000),@i int,@name char(4) declare @tb table (id int identity(1,1),_name char(4)) insert into @tb select distinct 班别 from tb select @i=1 while(@i<=(select count(*) from @tb)) begin select top 1 @name=_name from @tb where id not in (select top (@i-1) id from @tb) set @sql=isnull(@sql+','+@name+'=case 班别 when '''+@name+''' then temp end',@name+'=case 班别 when '''+@name+''' then temp end') set @i=@i+1 end set @sql='select 时间,'+@sql+' from ( select 时间,班别, temp=stuff((select '',''+rtrim(员工) from tb where 时间=t.时间 for xml path('''')),1,1,'''') from tb t group by 时间,班别 )t' exec(@sql)/*测试结果时间 晚班 中班 -------------------------------------------------------------- 2010-04-23 NULL 王梅,明月,孙红梅,陈红云 2010-04-24 NULL 陈红云,孙红梅,刘燕 2010-04-25 周晓丽,刘燕,王梅 NULL(3 行受影响) */
写一个函数专门取一个Task有多少人参与过不就行了
-------------------------------------------------------------------------- -- Author : htl258(Tony) -- Date : 2010-04-29 13:34:14 -- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) -- Jul 9 2008 14:43:34 -- Copyright (c) 1988-2008 Microsoft Corporation -- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3) -- Blog : http://blog.csdn.net/htl258 -------------------------------------------------------------------------- --> 生成测试数据表:usertaskIF NOT OBJECT_ID('[usertask]') IS NULL DROP TABLE [usertask] GO CREATE TABLE [usertask]([task_id] INT,[user_id] INT) INSERT [usertask] SELECT 2,101 UNION ALL SELECT 2,103 GO --SELECT * FROM [usertask]--> 生成测试数据表:taskIF NOT OBJECT_ID('[task]') IS NULL DROP TABLE [task] GO CREATE TABLE [task]([id] INT,[time] DATETIME,[task] NVARCHAR(10)) INSERT [task] SELECT 2,N'2010-04-29',N'完成作业' GO --SELECT * FROM [task]-->SQL查询如下: if object_id('dbo.f_str') is not null drop function dbo.f_str go create function dbo.f_str(@id int) returns varchar(100) as begin declare @str varchar(1000) select @str = isnull(@str + ' ','') + cast([user_id] as varchar) from [usertask] where [task_id] = @id return @str end goselect time,task,dbo.f_str(ID ) [user_id] from task/* time task user_id ----------------------- ---------- ---------------------- 2010-04-29 00:00:00.000 完成作业 101 103(1 行受影响) */
-------------------------------------------------------------------------- -- Author : htl258(Tony) -- Date : 2010-04-29 13:34:14 -- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) -- Jul 9 2008 14:43:34 -- Copyright (c) 1988-2008 Microsoft Corporation -- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3) -- Blog : http://blog.csdn.net/htl258 -------------------------------------------------------------------------- --> 生成测试数据表:usertaskIF NOT OBJECT_ID('[usertask]') IS NULL DROP TABLE [usertask] GO CREATE TABLE [usertask]([task_id] INT,[user_id] INT) INSERT [usertask] SELECT 2,101 UNION ALL SELECT 2,103 GO --SELECT * FROM [usertask]--> 生成测试数据表:taskIF NOT OBJECT_ID('[task]') IS NULL DROP TABLE [task] GO CREATE TABLE [task]([id] INT,[time] DATETIME,[task] NVARCHAR(10)) INSERT [task] SELECT 2,N'2010-04-29',N'完成作业' GO --SELECT * FROM [task]-->SQL2005查询如下: select time,task, [user_id]=ltrim((select ' '+ ltrim([user_id]) from [usertask] where [task_id]=[id] for xml path(''))) from task/* time task user_id ----------------------- ---------- ---------------------- 2010-04-29 00:00:00.000 完成作业 101 103(1 行受影响) */
合并列值 --******************************************************************************************* 表结构,数据如下: id value ----- ------ 1 aa 1 bb 2 aaa 2 bbb 2 ccc 需要得到结果: id values ------ ----------- 1 aa,bb 2 aaa,bbb,ccc 即:group by id, 求 value 的和(字符串相加) 1. 旧的解决方法(在sql server 2000中只能用函数解决。) --============================================================================= create table tb(id int, value varchar(10)) insert into tb values(1, 'aa') insert into tb values(1, 'bb') insert into tb values(2, 'aaa') insert into tb values(2, 'bbb') insert into tb values(2, 'ccc') go --1. 创建处理函数 CREATE FUNCTION dbo.f_strUnite(@id int) RETURNS varchar(8000) AS BEGIN DECLARE @str varchar(8000) SET @str = '' SELECT @str = @str + ',' + value FROM tb WHERE id=@id RETURN STUFF(@str, 1, 1, '') END GO -- 调用函数 SELECt id, value = dbo.f_strUnite(id) FROM tb GROUP BY id drop table tb drop function dbo.f_strUnite go /* id value ----------- ----------- 1 aa,bb 2 aaa,bbb,ccc (所影响的行数为 2 行) */ --=================================================================================== 2. 新的解决方法(在sql server 2005中用OUTER APPLY等解决。) create table tb(id int, value varchar(10)) insert into tb values(1, 'aa') insert into tb values(1, 'bb') insert into tb values(2, 'aaa') insert into tb values(2, 'bbb') insert into tb values(2, 'ccc') go -- 查询处理 SELECT * FROM(SELECT DISTINCT id FROM tb)A OUTER APPLY( SELECT [values]= STUFF(REPLACE(REPLACE( ( SELECT value FROM tb N WHERE id = A.id FOR XML AUTO ), ' <N value="', ','), '"/>', ''), 1, 1, '') )N drop table tb /* id values ----------- ----------- 1 aa,bb 2 aaa,bbb,ccc (2 行受影响) */ --SQL2005中的方法2 create table tb(id int, value varchar(10)) insert into tb values(1, 'aa') insert into tb values(1, 'bb') insert into tb values(2, 'aaa') insert into tb values(2, 'bbb') insert into tb values(2, 'ccc') go select id, [values]=stuff((select ','+[value] from tb t where id=tb.id for xml path('')), 1, 1, '') from tb group by id /* id values ----------- -------------------- 1 aa,bb 2 aaa,bbb,ccc (2 row(s) affected) */ drop table tb /
“动态值”的话!参考如下:原地址:http://topic.csdn.net/u/20100423/17/914d517d-a19e-4c75-bbf6-950188d80d58.html--------------------SQL Server数据格式化工具-------------------
---------------------------------------------------------------
-- DESIGNER :happycell188(喜喜)
-- QQ :584738179
-- Development Tool :Microsoft Visual C++ 6.0 C Language
-- FUNCTION :CONVERT DATA TO T-SQL
---------------------------------------------------------------
-- Microsoft SQL Server 2005
-- Developer Edition on Microsoft Windows XP [版本 5.1.2600]
---------------------------------------------------------------
---------------------------------------------------------------use test
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
时间 char(11),
员工 char(7),
班别 char(5)
)
go
--插入测试数据
insert into tb select '2010-04-23','王梅','早班'
union all select '2010-04-25','周晓丽','晚班'
union all select '2010-04-25','刘燕','晚班'
union all select '2010-04-25','王梅','晚班'
union all select '2010-04-23','明月','早班'
union all select '2010-04-24','陈红云','早班'
union all select '2010-04-24','孙红梅','早班'
union all select '2010-04-24','刘燕','早班'
union all select '2010-04-23','孙红梅','早班'
union all select '2010-04-23','陈红云','早班'
go
--代码实现declare @sql varchar(1000),@i int,@name char(4)
declare @tb table (id int identity(1,1),_name char(4))
insert into @tb select distinct 班别 from tb
select @i=1
while(@i<=(select count(*) from @tb))
begin
select top 1 @name=_name from @tb where id not in (select top (@i-1) id from @tb)
set @sql=isnull(@sql+','+@name+'=case 班别 when '''+@name+''' then temp end',@name+'=case 班别 when '''+@name+''' then temp end')
set @i=@i+1
end
set @sql='select 时间,'+@sql+' from (
select 时间,班别,
temp=stuff((select '',''+rtrim(员工) from tb where 时间=t.时间 for xml path('''')),1,1,'''') from tb t
group by 时间,班别 )t'
exec(@sql)/*测试结果时间 晚班 中班
--------------------------------------------------------------
2010-04-23 NULL 王梅,明月,孙红梅,陈红云
2010-04-24 NULL 陈红云,孙红梅,刘燕
2010-04-25 周晓丽,刘燕,王梅 NULL(3 行受影响)
*/
-- Author : htl258(Tony)
-- Date : 2010-04-29 13:34:14
-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
--------------------------------------------------------------------------
--> 生成测试数据表:usertaskIF NOT OBJECT_ID('[usertask]') IS NULL
DROP TABLE [usertask]
GO
CREATE TABLE [usertask]([task_id] INT,[user_id] INT)
INSERT [usertask]
SELECT 2,101 UNION ALL
SELECT 2,103
GO
--SELECT * FROM [usertask]--> 生成测试数据表:taskIF NOT OBJECT_ID('[task]') IS NULL
DROP TABLE [task]
GO
CREATE TABLE [task]([id] INT,[time] DATETIME,[task] NVARCHAR(10))
INSERT [task]
SELECT 2,N'2010-04-29',N'完成作业'
GO
--SELECT * FROM [task]-->SQL查询如下:
if object_id('dbo.f_str') is not null drop function dbo.f_str
go
create function dbo.f_str(@id int)
returns varchar(100)
as
begin
declare @str varchar(1000)
select @str = isnull(@str + ' ','') + cast([user_id] as varchar) from [usertask] where [task_id] = @id
return @str
end
goselect time,task,dbo.f_str(ID ) [user_id] from task/*
time task user_id
----------------------- ---------- ----------------------
2010-04-29 00:00:00.000 完成作业 101 103(1 行受影响)
*/
-- Author : htl258(Tony)
-- Date : 2010-04-29 13:34:14
-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
--------------------------------------------------------------------------
--> 生成测试数据表:usertaskIF NOT OBJECT_ID('[usertask]') IS NULL
DROP TABLE [usertask]
GO
CREATE TABLE [usertask]([task_id] INT,[user_id] INT)
INSERT [usertask]
SELECT 2,101 UNION ALL
SELECT 2,103
GO
--SELECT * FROM [usertask]--> 生成测试数据表:taskIF NOT OBJECT_ID('[task]') IS NULL
DROP TABLE [task]
GO
CREATE TABLE [task]([id] INT,[time] DATETIME,[task] NVARCHAR(10))
INSERT [task]
SELECT 2,N'2010-04-29',N'完成作业'
GO
--SELECT * FROM [task]-->SQL2005查询如下:
select time,task,
[user_id]=ltrim((select ' '+ ltrim([user_id]) from [usertask] where [task_id]=[id] for xml path('')))
from task/*
time task user_id
----------------------- ---------- ----------------------
2010-04-29 00:00:00.000 完成作业 101 103(1 行受影响)
*/
--*******************************************************************************************
表结构,数据如下:
id value
----- ------
1 aa
1 bb
2 aaa
2 bbb
2 ccc 需要得到结果:
id values
------ -----------
1 aa,bb
2 aaa,bbb,ccc
即:group by id, 求 value 的和(字符串相加) 1. 旧的解决方法(在sql server 2000中只能用函数解决。)
--=============================================================================
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
--1. 创建处理函数
CREATE FUNCTION dbo.f_strUnite(@id int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @str varchar(8000)
SET @str = ''
SELECT @str = @str + ',' + value FROM tb WHERE id=@id
RETURN STUFF(@str, 1, 1, '')
END
GO
-- 调用函数
SELECt id, value = dbo.f_strUnite(id) FROM tb GROUP BY id
drop table tb
drop function dbo.f_strUnite
go
/*
id value
----------- -----------
1 aa,bb
2 aaa,bbb,ccc
(所影响的行数为 2 行)
*/
--===================================================================================
2. 新的解决方法(在sql server 2005中用OUTER APPLY等解决。)
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
-- 查询处理
SELECT * FROM(SELECT DISTINCT id FROM tb)A OUTER APPLY(
SELECT [values]= STUFF(REPLACE(REPLACE(
(
SELECT value FROM tb N
WHERE id = A.id
FOR XML AUTO
), ' <N value="', ','), '"/>', ''), 1, 1, '')
)N
drop table tb /*
id values
----------- -----------
1 aa,bb
2 aaa,bbb,ccc (2 行受影响)
*/ --SQL2005中的方法2
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go select id, [values]=stuff((select ','+[value] from tb t where id=tb.id for xml path('')), 1, 1, '')
from tb
group by id /*
id values
----------- --------------------
1 aa,bb
2 aaa,bbb,ccc (2 row(s) affected) */ drop table tb /
2005里面的新语法 是XML的操作