求一存储过程

[yanick007]

表结构如下：
id dept times conten
1 销售部 2010-01-02 13:30:00 ppp
1 销售部 2010-01-03 12:00:00 ccc
2 开发部 2010-01-01 18:20:00 vvv
2 开发部 2010-02-10 15:15:00 eee
3 财务部 2010-03-18 10:00:00 yyy
----------------------------------------------------
要求输入以下结果：
dept oneMonth twoMonth threeMonth
销售部 ppp ccc
开发部 vvv eee
财务部 yyy
----------------------------------------------------
现有sql语句：
select id,dept,oneMonth = case(month(begin_time))
when 1 then content
end
,twoMonth= case(month(begin_time))
when 2 then content
end
,threeMonth= case(month(begin_time))
when 3 then content
end
……

[水族杰纶]

SQL code问题描述：
无论是在sql 2000，还是在 sql 2005 中,都没有提供字符串的聚合函数，
  所以，当我们在处理下列要求时，会比较麻烦：
有表tb, 如下：
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. 旧的解决方法

-- 1. 创建处理函数
CREATE FUNCTION dbo.f_str(@id int)
RETURNS varchar(8000)
AS
BEGIN
    DECLARE @r varchar(8000)
    SET @r = ''
    SELECT @r = @r + ',' + value
    FROM tb
    WHERE id=@id
    RETURN STUFF(@r, 1, 1, '')
END
GO
-- 调用函数

SELECt id, values=dbo.f_str(id) 
FROM tb 
GROUP BY id

-- 2. 新的解决方法 
-- 示例数据
DECLARE @t TABLE(id int, value varchar(10))
INSERT @t SELECT 1, 'aa'
UNION ALL SELECT 1, 'bb'
UNION ALL SELECT 2, 'aaa'
UNION ALL SELECT 2, 'bbb'
UNION ALL SELECT 2, 'ccc'

-- 查询处理
SELECT *
FROM(
    SELECT DISTINCT 
        id
    FROM @t
)A
OUTER APPLY(
    SELECT 
        [values]= STUFF(REPLACE(REPLACE(
            (
                SELECT value FROM @t N
                WHERE id = A.id
                FOR XML AUTO
            ), '<N value="', ','), '"/>', ''), 1, 1, '')
)N

/*--结果
id          values
----------- ----------------
1           aa,bb
2           aaa,bbb,ccc
(2 行受影响)
--*/

--各种字符串分函数

--3.3.1 使用游标法进行字符串合并处理的示例。
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3

--合并处理
--定义结果集表变量
DECLARE @t TABLE(col1 varchar(10),col2 varchar(100))

--定义游标并进行合并处理
DECLARE tb CURSOR LOCAL
FOR
SELECT col1,col2 FROM tb ORDER BY  col1,col2
DECLARE @col1_old varchar(10),@col1 varchar(10),@col2 int,@s varchar(100)
OPEN tb
FETCH tb INTO @col1,@col2
SELECT @col1_old=@col1,@s=''
WHILE @@FETCH_STATUS=0
BEGIN
    IF @col1=@col1_old
        SELECT @s=@s+','+CAST(@col2 as varchar)
    ELSE
    BEGIN
        INSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))
        SELECT @s=','+CAST(@col2 as varchar),@col1_old=@col1
    END
    FETCH tb INTO @col1,@col2
END
INSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))
CLOSE tb
DEALLOCATE tb
--显示结果并删除测试数据
SELECT * FROM @t
DROP TABLE tb
/*--结果
col1       col2
---------- -----------
a          1,2
b          1,2,3
--*/
GO


/*==============================================*/


--3.3.2 使用用户定义函数，配合SELECT处理完成字符串合并处理的示例
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3
GO

--合并处理函数
CREATE FUNCTION dbo.f_str(@col1 varchar(10))
RETURNS varchar(100)
AS
BEGIN
    DECLARE @re varchar(100)
    SET @re=''
    SELECT @re=@re+','+CAST(col2 as varchar)
    FROM tb
    WHERE col1=@col1
    RETURN(STUFF(@re,1,1,''))
END
GO

--调用函数
SELECT col1,col2=dbo.f_str(col1) FROM tb GROUP BY col1
--删除测试
DROP TABLE tb
DROP FUNCTION f_str
/*--结果
col1       col2
---------- -----------
a          1,2
b          1,2,3
--*/
GO

/*==============================================*/


--3.3.3 使用临时表实现字符串合并处理的示例
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3

--合并处理
SELECT col1,col2=CAST(col2 as varchar(100)) 
INTO #t FROM tb
ORDER BY col1,col2
DECLARE @col1 varchar(10),@col2 varchar(100)
UPDATE #t SET 
    @col2=CASE WHEN @col1=col1 THEN @col2+','+col2 ELSE col2 END,
    @col1=col1,
    col2=@col2
SELECT * FROM #t
/*--更新处理后的临时表
col1       col2
---------- -------------
a          1
a          1,2
b          1
b          1,2
b          1,2,3
--*/
--得到最终结果
SELECT col1,col2=MAX(col2) FROM #t GROUP BY col1
/*--结果
col1       col2
---------- -----------
a          1,2
b          1,2,3
--*/
--删除测试
DROP TABLE tb,#t
GO


/*==============================================*/

--3.3.4.1 每组 <=2 条记录的合并
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'c',3

--合并处理
SELECT col1,
    col2=CAST(MIN(col2) as varchar)
        +CASE 
            WHEN COUNT(*)=1 THEN ''
            ELSE ','+CAST(MAX(col2) as varchar)
        END
FROM tb
GROUP BY col1
DROP TABLE tb
/*--结果
col1       col2      
---------- ----------
a          1,2
b          1,2
c          3
--*/

--3.3.4.2 每组 <=3 条记录的合并
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3
UNION ALL SELECT 'c',3

--合并处理
SELECT col1,
    col2=CAST(MIN(col2) as varchar)
        +CASE 
            WHEN COUNT(*)=3 THEN ','
                +CAST((SELECT col2 FROM tb WHERE col1=a.col1 AND col2 NOT IN(MAX(a.col2),MIN(a.col2))) as varchar)
            ELSE ''
        END
        +CASE 
            WHEN COUNT(*)>=2 THEN ','+CAST(MAX(col2) as varchar)
            ELSE ''
        END
FROM tb a
GROUP BY col1
DROP TABLE tb
/*--结果
col1       col2
---------- ------------
a          1,2
b          1,2,3
c          3
--*/
GO
if not object_id('A') is null
    drop table A
Go
Create table A([id] int,[cname] nvarchar(2))
Insert A
select 1,N'张三' union all
select 2,N'李四' union all
select 3,N'王五' union all
select 4,N'蔡六'
Go
--> --> 
 
if not object_id('B') is null
    drop table B
Go
Create table B([id] int,[cname] nvarchar(5))
Insert B
select 1,N'1,2,3' union all
select 2,N'3,4'
Go
create function F_str(@cname nvarchar(100))
returns nvarchar(100)
as
begin 
select @cname=replace(@cname,ID,[cname]) from A where patindex('%,'+rtrim(ID)+',%',','+@cname+',')>0
return @cname
end
go
select [id],dbo.F_str([cname])[cname] from B

id          cname
----------- ----------------------------------------------------------------------------------------------------
1           张三,李四,王五
2           王五,蔡六

(2 個資料列受到影響)

[水族杰纶]

看走了
還要行列轉換~~

[水族杰纶]

先函數處理
放臨時表
再行列轉換吧

[百年树人]

---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[dept] varchar(6),[times] datetime,[content] varchar(3))
insert [tb]
select 1,'销售部','2010-01-02 13:30:00','ppp' union all
select 1,'销售部','2010-01-03 12:00:00','ccc' union all
select 2,'开发部','2010-01-01 18:20:00','vvv' union all
select 2,'开发部','2010-02-10 15:15:00','eee' union all
select 3,'财务部','2010-03-18 10:00:00','yyy'
go

create proc sp_test
as
select id,dept,oneMonth = isnull(case(month(times)) 
  when 1 then content 
end,'') 
,twoMonth= isnull(case(month(times)) 
  when 2 then content 
end,'') 
,threeMonth= isnull(case(month(times)) 
  when 3 then content 
end,'') 
from tb
go

exec sp_test
/**
id          dept   oneMonth twoMonth threeMonth 
----------- ------ -------- -------- ---------- 
1           销售部    ppp               
1           销售部    ccc               
2           开发部    vvv               
2           开发部             eee      
3           财务部                      yyy

（所影响的行数为 5 行）
**/

drop proc sp_test

[yanick007]

请大家看清楚，要求把content的值根据部门进行合并
求输入以下结果：
dept oneMonth twoMonth threeMonth
销售部 ppp ccc
开发部 vvv eee
财务部 yyy

[dawugui]

create table tb(id int, dept varchar(10), times datetime, conten varchar(10))
insert into tb values(1 , '销售部' , '2010-01-02 13:30:00' , 'ppp') 
insert into tb values(1 , '销售部' , '2010-01-03 12:00:00' , 'ccc') 
insert into tb values(2 , '开发部' , '2010-01-01 18:20:00' , 'vvv') 
insert into tb values(2 , '开发部' , '2010-02-10 15:15:00' , 'eee') 
insert into tb values(3 , '财务部' , '2010-03-18 10:00:00' , 'yyy')
go

create function dbo.f_str(@id int ,@dept varchar(10), @times varchar(7)) returns varchar(100)
as
begin
    declare @str varchar(1000)
    set @str = ''
    select @str = @str + ',' + cast(conten as varchar) from (select id , dept , conten , times = convert(varchar(7),times,120) from tb) t where id = @id and dept = @dept and times = @times
    set @str = right(@str , len(@str) - 1)
    return @str
end
go

--调用函数
select dept ,
  max(case times when '2010-01' then conten else '' end) [oneMonth],
  max(case times when '2010-02' then conten else '' end) [twoMonth],
  max(case times when '2010-03' then conten else '' end) [threeMonth]
from
(
  select id ,dept , times, conten = dbo.f_str(id ,dept , times) from (select id , dept , conten , times = convert(varchar(7),times,120) from tb) t group by id ,dept , times
) m
group by dept

drop function dbo.f_str
drop table tb

/*
dept       oneMonth                                                                                             twoMonth                                                                                             threeMonth                                                                                           
---------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- 
财务部                                                                                                                                                                                                                  yyy
开发部        vvv                                                                                                  eee                                                                                                  
销售部        ppp,ccc                                                                                                                                                                                                   

（所影响的行数为 3 行）

*/

[百年树人]

[Quote=引用 4 楼 josy 的回复:]
SQL code---测试数据---ifobject_id('[tb]')isnotnulldroptable[tb]gocreatetable[tb]([id]int,[dept]varchar(6),[times]datetime,[content]varchar(3))insert[tb]select1,'销售部','2010-01-02 13:30:00','ppp'unionallsel?-
[/Quote]
我也看错了，修正一下

create proc sp_test
as
select id,dept,oneMonth = isnull(case times 
  when 1 then content 
end,'')
,twoMonth= isnull(case times 
  when 2 then content 
end,'')
,threeMonth= isnull(case times  
  when 3 then content 
end,'')
from(
select id,dept,month(times) as times,
content=stuff((select ','+content from tb where id=t.id and dept=t.dept and month(times)=month(t.times) for xml path('')),1,1,'')
from tb t
group by id,dept,month(times)
) t
go

[百年树人]

exec sp_test
/**
id      dept   oneMonth twoMonth threeMonth 
1	销售部	ppp,ccc		
2	开发部	vvv		
2	开发部		eee	
3	财务部			yyy
**/

[nianran520]

--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([id] int,[dept] varchar(6),[times] datetime,[content] varchar(3))
insert [tb]
select 1,'销售部','2010-01-02 13:30:00','ppp' union all
select 1,'销售部','2010-01-03 12:00:00','ccc' union all
select 2,'开发部','2010-01-01 18:20:00','vvv' union all
select 2,'开发部','2010-02-10 15:15:00','eee' union all
select 3,'财务部','2010-03-18 10:00:00','yyy'

select * from [tb]

--函数
create function dbo.getcontent(@id int,@month int)
returns varchar(8000)
as
begin
	declare @str varchar(8000)
	select @str=isnull(@str+' ','')+content from [tb] where id=@id and month(times)=@month
	return @str
end
--查询
declare @sql varchar(8000)
select @sql=isnull(@sql+',','')+'max(case month(times) when '+ltrim(times)+' then dbo.getcontent(id,month(times)) else '''' end) as ['+ltrim(times)+']'
from (select distinct  month(times) as times from [tb]) t
select @sql='select id,dept,'+@sql+' from tb group by id,dept order by id'
exec(@sql)

[mjluoying]

呵呵

[bancxc]

create table tb(id int,dept varchar(10),times datetime,conten varchar(10))
insert into tb select 
1,'销售部','2010-01-02 13:30:00','ppp' union all select
1,'销售部','2010-01-03 12:00:00','ccc' union all select
2,'开发部','2010-01-01 18:20:00','vvv' union all select
2,'开发部','2010-02-10 15:15:00','eee' union all select
3,'财务部','2010-03-18 10:00:00','yyy' 
 
 
DECLARE @STR VARCHAR(1000)
SELECT @STR='select dept'
SELECT @STR=@STR+',['+ LTRIM(M) + ']=MAX(CASE MONTH(TIMES) WHEN '+LTRIM(M)+' THEN CONTEN ELSE '''' END)'
FROM (SELECT DISTINCT MONTH(TIMES) M FROM TB) T

select @STR = @str + ' from (
select dept,times,conten=stuff(b.x,1,1,'''') 
from
(
select distinct dept,times
from tb) a cross apply (select '',''+conten  from tb b where a.dept=b.dept  and month(a.times)=month(b.times) 
for xml path('''')) b(x)
)
tb group by dept' 
exec(@STR) 

[yanick007]

谢谢大家！结贴了

[bancxc]

create table tb(id int,dept varchar(10),times datetime,conten varchar(10))
insert into tb select 
1,'销售部','2010-01-02 13:30:00','ppp' union all select
1,'销售部','2010-01-03 12:00:00','ccc' union all select
2,'开发部','2010-01-01 18:20:00','vvv' union all select
2,'开发部','2010-02-10 15:15:00','eee' union all select
3,'财务部','2010-03-18 10:00:00','yyy' 
 
 
DECLARE @STR VARCHAR(1000)
SELECT @STR='select dept'
SELECT @STR=@STR+',['+ LTRIM(M) + ']=MAX(CASE MONTH(TIMES) WHEN '+LTRIM(M)+' THEN CONTEN ELSE '''' END)'
FROM (SELECT DISTINCT MONTH(TIMES) M FROM TB) T

select @STR = @str + ' from (
select dept,times,conten=stuff(b.x,1,1,'''') 
from
(
select distinct dept,times
from tb) a cross apply (select '',''+conten  from tb b where a.dept=b.dept  and month(a.times)=month(b.times) 
for xml path('''')) b(x)
)
tb group by dept' 
exec(@STR) 

财务部			     yyy
开发部	vvv	    eee	
销售部	ppp,ccc