'stuff' 和 'for xml path('')' 来自 Postgresql 中的 SQL Server
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14119517/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
'stuff' and 'for xml path('')' from SQL Server in Postgresql
提问by user1891262
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:
我正在将一些 SQL Server 2008R2 查询迁移到 Postgresql 9.0，但遇到了一些问题。这是 SQL Server 查询：
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
Reading SQL Server documentation I understand that this creates a comma separated list. I think that I can change stufffunction to overlayfunction in Postresql'. Am I correct?
阅读 SQL Server 文档我知道这会创建一个逗号分隔的列表。我认为我可以将stuff函数更改为overlayPostresql' 中的函数。我对么？
The second problem comes with SQL Server's for xml pathwith ('') as a parameter. It returns the values assigned to an attribute called pNAMESinstead of create row elements. Is that correct?
第二个问题是 SQL Serverfor xml path以 ('') 作为参数。它返回分配给名为pNAMES而不是创建行元素的属性的值。那是对的吗？
Does Postgresql Query_to_xml()function with attribute tableforest = 'true'do the same?
Query_to_xml()具有属性的Postgresql函数是否tableforest = 'true'也做同样的事情？
Thank you.
谢谢你。
回答by Mikael Eriksson
You can use string_agginstead.
你可以string_agg改用。
PostgreSQL 9.1.6 Schema Setup:
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');
Query 1:
查询 1：
select string_agg(Name, ',') as Names
from T
结果：
| NAMES |
---------------------
| Kalle,Pelle,Urban |
回答by kashif
You can replace it by String_agg for example SQL Server stored procedure:
您可以用 String_agg 替换它，例如 SQL Server 存储过程：
STUFF((SELECT DISTINCT ', ' + CONVERT(VARCHAR,L.ROLE_SUB_CAT_ID)
FROM [PHS].[dbo].PHS_ADMIN_USER_ACCESS_DTL K,
[PHS].[dbo].[PHS_ADMIN_USER_ROLE_SUB_CAT_MST] L
WHERE L.ROLE_SUB_CAT_ID = K.ROLE_SUB_CAT_ID
AND K.UMC_ID = A.UMC_ID
AND K.CCR_ID = A.CCR_ID
FOR XML PATH('')), 1, 1, '') AS ROLE_SUB_CAT_ID
Convert it to postgresql like this:
将其转换为 postgresql，如下所示：
string_agg((SELECT distinct ', ' || cast(L.ROLE_SUB_CAT_ID as VARCHAR) FROM PHS.dbo.PHS_ADMIN_USER_ACCESS_DTL K,
PHS.dbo.PHS_ADMIN_USER_ROLE_SUB_CAT_MST L
WHERE L.ROLE_SUB_CAT_ID = K.ROLE_SUB_CAT_ID AND K.UMC_ID = A.UMC_ID AND K.CCR_ID=A.CCR_ID
), 1, 1, '') AS ROLE_SUB_CAT_ID
