Sean Lange (6/26/2013)
kapil_kk (6/26/2013)
you can do the same thing as Sean did using SUBSTRING also:SELECT id,
SUBSTRING((SELECT ', ' + Term
FROM Something s2
WHERE s1.id = s2.id
and s2.Type = 'Subject'
ORDER BY s2.Term
FOR XML PATH('')), 2, 1000) as Result
FROM Something s1
where Type = 'Subject'
GROUP BY id
The problem here is that you have to choose an arbitrary length. In your case it was 1000 which is plenty to cover the sample data. The issue can be demonstrated by changing the length to something shorter than the output you can see what happens.
Let's change the length to 10 instead 1000.
SELECT id,
Substring((SELECT ', ' + Term
FROM Something s2
WHERE s1.id = s2.id
AND s2.Type = 'Subject'
ORDER BY s2.Term
FOR XML PATH('')), 2, 10) AS Result
FROM Something s1
WHERE Type = 'Subject'
GROUP BY id
Yes, Sean changing length can arise problem...thanks for correcting that
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/