在一個論壇上有人發個帖子問這個問題：
有一表的記錄為：
Task_ID Employee_Name STEPWORKTIME
6262 張三 2
6262 李四 2
6262 王二 1.5
6265 成某 2
6265 趙某 2
6265 錢某 1.5
……
我想求一個函數能將Task_ID字段相同記錄合並為一條記錄。
即想得到如下結果：
Task_ID Employees WorkLoad
6262 張三、李四、王二 5.5
6265 成某、趙某、錢某 5.5
……
我寫了一下，沒寫出UDF，只寫了個存儲過程。能應付他大概的要求。
可是樓主不怎麼滿意，想著與其丟掉，不如索性貼出來，大家批評批評^_^。
先創建一個表，名為FromTable
db2 create table FromTable(id varchar(10),name varchar(200),stepworktime int)
插入數據
db2 insert into FromTable values ('6262','張三',2)
db2 insert into FromTable values ('6262','李四',2)
db2 insert into FromTable values ('6262','王二',1.5)
db2 insert into FromTable values ('6265','成某',2)
db2 insert into FromTable values ('6265','趙某',2)
db2 insert into FromTable values ('6265','錢某',1.5)
現在再創建一個表，為ToTable
跟test1000一樣的結構，用一個存儲過程把你要的結果插進去
創表
db2 create TABLE ToTable ( id varchar(100), name varchar(100),sum int )
寫存儲過程
CREATE PROCEDURE ADMINISTRATOR.ProcConcatName ( )
------------------------------------------------------------------------
--SQL 存儲過程
--Sisijian
--2005-01-20
------------------------------------------------------------------------
Lable1: begin
------------------------------------------------------------------------
--定義變量
--v_NumOfRecd存放對應FromTable每個id記錄條數
--v_Index控制當前記錄是在id相同的記錄中第幾條
--v_id等三個變量用於存放臨時數據
--at_end控制是否到底
------------------------------------------------------------------------
DECLARE SQLSTATE CHAR(5);
DECLARE v_NumOfRecd int;
DECLARE v_Index int;
DECLARE v_Id varCHAR(100);
DECLARE v_ConcatedName varchar(5000);
DECLARE v_SumOfWorkTime int;
DECLARE at_end INT DEFAULT 0;
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE C1 CURSOR FOR
SELECT id, count(*)
FROM FromTable
GROUP BY id
ORDER BY id;
DECLARE CONTINUE HANDLER FOR not_found
SET at_end = 1;
------------------------------------------------------------------------
--游標移動一次，就到一個新的id，id不會重復，因為經過上面的group by
------------------------------------------------------------------------
OPEN C1;
Concat_Loop:
LOOP
FETCH C1 INTO v_Id, v_NumOfRecd;
IF at_end = 1 THEN
LEAVE Concat_Loop;
END IF;
------------------------------------------------------------------------
--遇到每個id ，第一條記錄都應該直接插入的
------------------------------------------------------------------------
SET v_Index=1;
SET v_ConcatedName = (SELECT name FROM
(SELECT ROW_NUMBER() over() as a ,FromTable.* FROM FromTable where id = v_Id) as x where a=v_Index );
SET v_SumOfWorkTime = (SELECT STEPWORKTIME FROM
(SELECT ROW_NUMBER() over() as a ,FromTable.* FROM FromTable where id = v_Id) as x where a=v_Index );
INSERT INTO ToTable VALUES (v_Id, v_ConcatedName,v_SumOfWorkTime);
SET v_Index=2;
------------------------------------------------------------------------
--如果有第二條的話，就連接名字字符串，累加STEPWORKTIME數據
------------------------------------------------------------------------
Inner_Loop:
LOOP
IF v_Index = (v_NumOfRecd+1) THEN
LEAVE Inner_Loop;
ELSE
SET v_ConcatedName = v_ConcatedName||','||(SELECT name FROM
(SELECT ROW_NUMBER() over() as a,FromTable.* FROM FromTable where id = v_Id) as x where a=v_Index );
SET v_SumOfWorkTime = v_SumOfWorkTime+(SELECT STEPWORKTIME FROM
(SELECT ROW_NUMBER() over() as a ,FromTable.* FROM FromTable where id = v_Id) as x where a=v_Index );
UPDATE ToTable SET name = v_ConcatedName where id = v_Id;
UPDATE ToTable SET num = v_SumOfWorkTime where id = v_Id;
SET v_Index=v_Index+1;
END IF ;
END LOOP Inner_Loop;
END LOOP Concat_Loop;
CLOSE C1;
END Lable1
我的數據類型設錯了，所以運行改存儲過程後，查詢ToTable表結果會有點點出入。
D:\>db2 select * from totable
ID
NAME
SUM
--------------------------------------------------------------------------------
-------------------- -----------------------------------------------------------
----------------------------------------- -----------
6262
張三,李四,王二
5
6265
趙某,錢某,成某
5
2 條記錄已選擇。
寫得不好，請大家多體諒下呵：）
