sql语句——连接同一字段的所有值

编辑小王 · 发表于 2014-11-10 17:08:56

Oracle可以用SYS_CONNECT_BY_PATH字符串聚合函数:
SELECT LTRIM(MAX(SYS_CONNECT_BY_PATH(productname, ', ')), ', ') AS productname
FROM(
SELECT '1' as id, productname,
ROW_NUMBER() OVER (PARTITION BY '1' ORDER BY productname) AS curr,
ROW_NUMBER() OVER (PARTITION BY '1' ORDER BY productname) -1 AS prev
FROM products
-- where 条件
order by productname
)
START WITH curr = 1
CONNECT BY prev = PRIOR curr AND id = PRIOR id
GROUP BY id
但MS SQL 2000没有这种函数,但可以通过自定义函数或过程来实现.
-- drop function ConnectString
Create FUNCTION ConnectString( @name varchar(20) )
RETURNS varchar(1024)
AS
BEGIN
declare @Str varchar(1024)
set @Str = ''
select @Str = @Str + (case when @Str = '' then '' else ',' end) + [productname] from products
--where [productname] = @name
return @Str
END
GO
-- 调用
select DISTINCT dbo.ConnectString(productname) from products
SQL Server 2005 可用OUTER APPLY:
以下是从网上摘录的:
-- 示例数据
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 行受影响)
--*/

复制代码

收藏本站