该函数显示值的组合，并以逗号分隔 [英] function which displays the combination of values with comma separated between them

问题描述

对于我的应用程序

我有一个具有两列ID和Applicant_Member_Id的表，其数据为

ID Applicant_Member_Id

117 1
117 2
186 3
186 4
223 5
224 6
556 7
556 8
186 9
-
-
-
我想要一个将输出返回为
的函数 117 1,2
186 3,4
556 7,8
223 5
224 6

for my application

i have a table with two columns ID and Applicant_Member_Id having data as

ID Applicant_Member_Id

117 1
117 2
186 3
186 4
223 5
224 6
556 7
556 8
186 9
-
-
-
i want a function that which returns the output as
117 1,2
186 3,4
556 7,8
223 5
224 6

推荐答案

无此功能
您可以使用将使用FOR XML PATH('''')SQL的内置函数合并值的查询

There is not a function for this
you can use query that will combine value using FOR XML PATH('''') Inbuilt Function of SQL

select ID, substring(Applicant_Member_Ids,1,len(Applicant_Member_Ids)-1) from
(
    select ID ,
    (select convert(varchar, Applicant_Member_Id) + ','  as [text()] from YourTable where ID =a.ID  for xml path(''))  as Applicant_Member_Ids
    from YourTable a
    group by ID
) as temp

祝您编码愉快！
:)

Happy Coding!
:)

尝试一下...

Try this...

DECLARE @ApplicantTable TABLE
(
  ID INT,
  ApplicantId INT
)

INSERT INTO @ApplicantTable VALUES
(117,1),
(117 ,2),
(186 ,3),
(186 ,4),
(223, 5),
(224, 6),
(556, 7),
(556, 8),
(186, 9)

SELECT ID,STUFF(( SELECT ',' + CAST(ApplicantId AS VARCHAR) FROM @ApplicantTable 
WHERE ID = A.Id FOR XML PATH('') ),1,1,'')Ids
FROM @ApplicantTable A GROUP BY ID

并根据组合获得行号....

And to get the rownumber according to the combination....

SELECT ID,ROW_NUMBER() OVER(ORDER BY Ids) RowNum,IDs FROM (
SELECT ID,STUFF(( SELECT ',' + CAST(ApplicantId AS VARCHAR) FROM @ApplicantTable 
WHERE ID = A.Id FOR XML PATH('') ),1,1,'')Ids
FROM @ApplicantTable A GROUP BY ID ) A

谢谢

这篇关于该函数显示值的组合，并以逗号分隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！