Try this:
SELECT Name, Id=STUFF((SELECT ',' + CONVERT(VarChar(10), Id)
FROM myTable b
WHERE b.Name = a.Name
FOR XML PATH('')), 1, 1, '')
FROM myTable a
GROUP BY Name
[edit]Typo: "2" for "1" in STUFF 3rd parameter - OriginalGriff)
"Hey thank you so much for your reply, it still only gives single Id's. The example I have above all the names are individual entries in the same table in the data base. So for instance I need to look for all instances of lets say 'Airtel'. So there are two names which start with Airtel in that table Id's 1,9. Any idea how to display this. Thanks again for your help."
That's what it does, in the only way you can if you don't know how many IDs there are per Name.
If I try it:
Name PaidAmount
AAAA 500
BBBB 750
AAAA 150
CCCC 400
DDDD 300
AAAA 100
CCCC 350
DDDD 450
XXX 666
XXX 666
XXX 666
XXX 666
XXX 666
XXX 666
XXX 666
XXX 666
And execute
SELECT Name, PaidAmount=
STUFF((SELECT ',' + CONVERT(VarChar(10), PaidAmount)
FROM myTable b
WHERE b.Name = a.Name
FOR XML PATH('')), 1, 1, '')
FROM myTable a
GROUP BY NameThen I get:
Name PaidAmount
AAAA 500,150,100
BBBB 750
CCCC 400,350
DDDD 300,450
XXX 666,666,666,666,666,666,666,666
Which is exactly what you wanted...
I suspect that you got the names wrong, or didn't think about what it is doing...:laugh:
Submit an article or tip