表1.有下面一個表的資料
dept_line order_no o_num p_num
部門一 200712254 4920.00 2788.00
部門二 200712254 4920.00 2132.00
部門四 200712275 2132.00 1000.00表2.我要的結果如下:
order_no o_num p_num dept_line
200712254 4920.00 4920.00 部門一,部門二
200712275 2132.00 1000.00 部門四注:在表1中按order_no和o_num分組,求和sum(p_num),並且用逗號連接dept_line;
最好用一句SQL語句
dept_line order_no o_num p_num
部門一 200712254 4920.00 2788.00
部門二 200712254 4920.00 2132.00
部門四 200712275 2132.00 1000.00表2.我要的結果如下:
order_no o_num p_num dept_line
200712254 4920.00 4920.00 部門一,部門二
200712275 2132.00 1000.00 部門四注:在表1中按order_no和o_num分組,求和sum(p_num),並且用逗號連接dept_line;
最好用一句SQL語句
--处理的数据
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
from tb
group by order_no,o_num
RETURNS varchar(100)
AS
BEGIN
DECLARE @re varchar(100)
SET @re=''
SELECT @re=@re+','+dept_line
FROM tb
WHERE order_no=@order_no
RETURN(STUFF(@re,1,1,''))
END
GO
select order_no,sum(o_num) o_num,sum(p_num) p_num,dbo.f_str(order_no) dept_line
from tb
group by order_no
RETURNS varchar(100)
AS
BEGIN
DECLARE @re varchar(100)
SET @re=''
SELECT @re=@re+','+dept_line
FROM tb
WHERE order_no=@order_no and p_num=@p_num
RETURN(STUFF(@re,1,1,''))
END
GO
select order_no,sum(o_num) o_num, p_num,dbo.f_str(order_no) dept_line
from tb
group by order_no,p_num
returns varchar(800)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+dept_line from tb where order_no=@order_no and o_num=@o_num from tb
return @str
end
RETURNS varchar(100)
AS
BEGIN
DECLARE @re varchar(100)
SET @re=''
SELECT @re=@re+','+dept_line
FROM tb
WHERE order_no=@order_no and p_num=@p_num
RETURN(STUFF(@re,1,1,''))
END
GO
select order_no,sum(o_num) o_num, p_num,dbo.f_str(order_no,p_num) dept_line
from tb
group by order_no,p_num