如何在Select语句中将相同列值的某些行连接成一个 [英] How to concatenate the some rows of same columns values into one in the Select statement

问题描述

嗨SQL专家，



我有一张名为Card的表它包含2列，即Cardnumber，PersonID





它包含如下所示的值：



卡表：

CardNumber PersonID

1 200

2 200

3 300



Cardnumber列包含唯一我想要输出如下所示：

PersonID ConcatedNatedCardnumbersByPersonIDWise



200 1,2

$ 3

  SELECT  PersonID，ConcatedNatedCardnumbersByPersonIDWise 来自卡

我怎样才能做到这一点。

请帮助我。我将等待有价值的回复，如果你帮我解决这个问题，我将不胜感激。

解决方案

试试这个

 选择 PersonID，STUFF（（ SELECT  ' ]，[' + CardNumber 
  FROM  [Card 表] 
 其中 [Card 表] .PersonID = t2.PersonID 
 订单 按 CardNumber 
  FOR  XML PATH（' '））， 1 ， 2 ，' '）+ ' ]' 来自 [Card 表]  as  t2  group   by  PersonID 

试试这个

 选择 PersonID，STUFF（
（选择 ' ，' + CardNumber 来自 TempTable1 sch 
 其中 sch.PersonID = sch1.PersonID 
  FOR  XML PATH（' '））
， 1 ， 1 ，' '）来自 TempTable1 sch1 
  group   by  PersonID 
  order   by   1  

Hi SQL Experts,

I have table called Card it contains the 2 columns namely Cardnumber, PersonID


It contains the values as shown in the below:

Card Table:
CardNumber PersonID
1 200
2 200
3 300

Cardnumber column contains unique values.

I want the output as shown in the below:
PersonID ConcatedNatedCardnumbersByPersonIDWise

200 1 ,2
300 3

SELECT  PersonID, ConcatedNatedCardnumbersByPersonIDWise from card 

How can i achieve this.
Please help me. I will be waiting for valuable replies, i will appreciate you if you help me out with this issue.

解决方案

Try this

select PersonID,STUFF(( SELECT '],[' + CardNumber
FROM         [Card Table]
Where [Card Table].PersonID = t2.PersonID
Order by CardNumber
FOR XML PATH('')), 1, 2, '') + ']' from [Card Table] as t2 group by PersonID

Try this

select PersonID, STUFF(
(select ','+ CardNumber from TempTable1 sch
where sch.PersonID = sch1.PersonID
FOR XML PATH(''))
,1,1,'') from TempTable1 sch1
group by PersonID
order by 1

这篇关于如何在Select语句中将相同列值的某些行连接成一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！