I have this query, our friend "OMG Ponies" help me to make it :) :-
SELECT t.id AS taskid,
STUFF(
(
SELECT ',' + x.tID
FROM (
SELECT CAST(id AS VARCHAR(200)) AS tid
FROM CRSTask c WHERE c.ParentTask =
7562 -- comment this line
) x
FOR XML PATH('')
),
1,
1,
''
) AS strIDS
FROM CRSTask t
WHERE t.ParentTask IS NULL
AND t.id = 7562 -- comment this line
the result of this query will be:
id strIDS
7562 7615,7616,7617,7618,7619,7620,7621,7631,7632,123
this is good.
but when i tried to replace the strIDS with its names from another table it take alot of time.
e.g. 123 [the last id in strIDS] is a taskID from CRSTask, has a relation ont-to-one to CRSTaskReceiver and taskReceiver has a relation one-to-one to Portal_Users_View(id,userName) -I need to replace 123 with its equivalent userName -I made it using Joins .. it take a lot of time, also using Where between tables take alot of time