如何将多行中的所有值合并到一行中?
问题描述:
可以说我有查询:如何将多行中的所有值合并到一行中?
SELECT Foo FROM Bar
它返回
富
一个
乙
Ç
我真正的是:
富
A,B,C
因此,所有行的所有值都被折叠为一行(逗号是可选的)。
有没有办法使用select语句来做到这一点,因为我不想使用游标?
答
SELECT
(
SELECT
CASE
WHEN ROW_NUMBER() OVER(ORDER BY bar) = 1 THEN ''
ELSE ', '
END + CAST(bar AS VARCHAR)
FROM foo
ORDER BY bar
FOR XML PATH('')
)
答
DECLARE @foos VARCHAR(4000)
SELECT @foos = COALESCE(@foos + ',', '') + Foo FROM Bar
SELECT @foos AS Foo
答
罗斯,
这应该让你开始。
DECLARE @r VARCHAR(8000)
SELECT @r = (SELECT DISTINCT Foo + ', ' FROM Bar FOR XML PATH(''))
IF @r IS NOT NULL AND @r <> '' SET @r = SUBSTRING(@r, 1, LEN(@r)-1)
SELECT @r
答
请尝试以下
declare @joined varchar(max)
set @joined = ''
select @joined = @joined + IsNull(Foo + ',', '')
from Bar (nolock)
--; Drop last "," if necessary
set @joined = substring(@joined, 1, len(@joined) - (case when len(@joined) > 0 then 1 else 0 end))
select @joined as foo
答
select max(a),max(b),max(c) from
(
select 'a' as a,null as b,null as c
union
select null,'b',null
union
select null,null,'c'
) as x
这看起来“优雅”但很慢 – Sung 2009-02-25 16:01:05