来自Postgresql中SQL Server的'stuff'和'for xml path('')' [英] 'stuff' and 'for xml path('')' from SQL Server in Postgresql
问题描述
我正在将一些SQL Server 2008R2查询迁移到Postgresql 9.0,但遇到了一些麻烦。
这是SQL Server查询:
I'm migrating some SQL Server 2008R2 queries to Postgresql 9.0 and I have some trouble with it. Here's the SQL Server query:
stuff((select ', '+p.[NAME] as 'data()'
from BPROVIDERS_PROVIDER p, BORDER_ARTICLEORDERPROVIDER aop
where p.OID = aop.PROVIDER for xml path('')),1,1,'')) as pNAMES
阅读SQL Server文档我知道这会创建一个逗号分隔的列表。我认为我可以在Postresql中将东西
函数更改为覆盖
函数。我是正确的吗?
Reading SQL Server documentation I understand that this creates a comma separated list. I think that I can change stuff
function to overlay
function in Postresql'. Am I correct?
第二个问题是SQL Server的 xml路径
带有('')作为参数。它返回分配给名为 pNAMES
的属性的值,而不是创建行元素。那是对的吗?
The second problem comes with SQL Server's for xml path
with ('') as a parameter. It returns the values assigned to an attribute called pNAMES
instead of create row elements. Is that correct?
执行Postgresql Query_to_xml()
函数,属性为 tableforest ='true'
也这样做吗?
Does Postgresql Query_to_xml()
function with attribute tableforest = 'true'
do the same?
谢谢。
推荐答案
您可以改用 string_agg
。
PostgreSQL 9.1.6模式设置:
create table T
(
Name varchar(10)
);
insert into T values('Kalle');
insert into T values('Pelle');
insert into T values('Urban');
查询1 :
select string_agg(Name, ',') as Names
from T
$ b中选择string_agg(Name,',')作为名称
$ b
结果 :
Results:
| NAMES |
---------------------
| Kalle,Pelle,Urban |
这篇关于来自Postgresql中SQL Server的'stuff'和'for xml path('')'的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!