如何在子查询中使用 Coalesce 进行字符串连接？

发布于 2024-10-31 20:13:35 字数 669 浏览 3 评论 0原文

我正在尝试使用“Coalesce”将一个表中的多个行值连接起来，用逗号分隔，并将其列为子查询中的一列。

的信息

Declare @assignTo nvarchar(4000)

Select 
table1.columnA
table1.columnB
(
select @assignTo = Coalesce(@assignTo + ', ', '') + CAST(Name as nvarchar(250))
from
table2    
where
...
)
from table1
where

沿着......

我不断收到“'='附近的语法不正确”

。如果我只是尝试执行调用 Coalesce 函数的子查询，那就没问题。即，

 Declare @assignTo nvarchar(4000) 
 select @assignTo = Coalesce(@assignTo + ', ', '') + CAST(Name as nvarchar(250))
    from
    table2    
    where
    ...
  Select @assignTo

没关系。所以我的问题是，如何将其包含为子查询？

非常感谢

ps：这是 SQL Server 2000 特有的。

原文

I'm trying to string concatenate multiple row values from one table using "Coalesce", separated by comma and list it as a column in a subquery.

Something along the line of

Declare @assignTo nvarchar(4000)

Select 
table1.columnA
table1.columnB
(
select @assignTo = Coalesce(@assignTo + ', ', '') + CAST(Name as nvarchar(250))
from
table2    
where
...
)
from table1
where

.....

I keep getting "Incorrect syntax near '='."

If I just try to execute the subquery where the Coalesce function is called, its' fine. i.e

 Declare @assignTo nvarchar(4000) 
 select @assignTo = Coalesce(@assignTo + ', ', '') + CAST(Name as nvarchar(250))
    from
    table2    
    where
    ...
  Select @assignTo

That's fine. So my question is, how do I include it as a subquery?

Thanks a lot

ps: This is specific to SQL server 2000.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

萌吟 2024-11-07 20:13:35

您不能将其包含为子查询：您必须将其移至 UDF 中。

在 SQL Server 2005 中，您可以使用 XML PATH 技术。但对于 SQL Server 2000，您必须有一个单独的标量 UDF，具有表访问和串联功能

回复收藏 0 原文

帅气尐潴 2024-11-07 20:13:35

据我所知，
如果您喜欢作为子查询，您可以这样做。
但上面的解决方案是最方便的。

Declare @assignTo nvarchar(4000)

Select 
table1.columnA
table1.columnB
tmp.[c]
from table1,
     (
      select @assignTo = Coalesce(@assignTo + ', ', '') + CAST(Name as nvarchar(250)) as [c]
      from
      table2    
      where
      ...
      ) as tmp
where

希望它有效！

As far as I know,
You can do like this if u prefer to do as SubQuery.
But the above solution is the most convenient one.

Declare @assignTo nvarchar(4000)

Select 
table1.columnA
table1.columnB
tmp.[c]
from table1,
     (
      select @assignTo = Coalesce(@assignTo + ', ', '') + CAST(Name as nvarchar(250)) as [c]
      from
      table2    
      where
      ...
      ) as tmp
where

Hope it works!

回复收藏 0 原文

~没有更多了~