Oracle合并多行

很多时候都在用这个，而没记录一下，存起来吧，非常好用，不用自己写了

WMSYS.WM_CONCAT


附上SQLSERVER的



SELECT DC.DIST_ID,DDP.CLIENT_ID,DDP.BILL_DATE,DDP.PROD_CODE,DDP.PROD_UNIT,DDP.PROD_QUANTITY,DPM.TARGET_PROD_CODE,DPM.TARGET_UNIT_ID,DPM.TARGET_PROD_UNIT
FROM DMS_DATA_PURCHASE DDP
INNER JOIN DMS_PROD_MAPPING DPM
ON  DDP.CLIENT_ID = DPM.CLIENT_ID
AND DDP.PROD_CODE = DPM.DIST_PROD_CODE
AND DDP.PROD_UNIT = DPM.DIST_PROD_UNIT
AND DDP.DELETE_TIME IS NOT NULL
--AND (DDP.CLIENT_ID = 393  OR DDP.CLIENT_ID = 394)
AND DDP.BILL_DATE BETWEEN '2011-03-01' AND '2011-03-31 23:59:59'
INNER JOIN DMS_CLIENT DC
ON DC.CLIENT_ID = DDP.CLIENT_ID
AND DC.DIST_ID  = 456
ORDER BY DC.DIST_ID, DDP.BILL_DATE DESC


SELECT TOP 10 *
FROM DMS_DATA_PURCHASE DDP
WHERE DDP.DELETE_TIME IS NOT NULL

SELECT *
FROM DMS_PROD_MAPPING DPM









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


--1. 创建处理函数
CREATE FUNCTION dbo.F_WM_CONCAT(@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_WM_CONCAT(id) FROM tb GROUP BY id

drop table tb
drop function dbo.F_WM_CONCAT
go