quote:

---

Originally posted by visakh16

you need to use a CTE for this

have a look at

http://msdn.microsoft.com/en-us/library/ms186243.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

quote:

---

Originally posted by Furrukh

quote:

---

Originally posted by visakh16

you need to use a CTE for this

have a look at

http://msdn.microsoft.com/en-us/library/ms186243.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

Hi Visak16,

I already knew that I need CTE hence the title says recursive query.

I want to see result as follows

PART_NO REPLACEMENT_LIST
999
123
456 123
789 999
777 789,456,123,999
888 777,789,456,123,999

this is complete hirerchy of replaced parts. like part 777 has replaced 456 and 789 and 456 did replaced 123 simillarly 789 replaced 999 hence result should contain all possible replacement as

777 789,456,123,999

please note 777 directly replaced 789 and 456 but indirectly it also is the replacement for 123 and 999.

---

quote:

---

Originally posted by namman

Furrukh

Do you have solution yet ?

---

quote:

---

Originally posted by Furrukh

quote:

---

Originally posted by namman

Furrukh

Do you have solution yet ?

---

no not yet. I am surprise that no one on this forum can answer simple question. but thanks anyways for asking.

---

;WITH cte1 AS
(
	SELECT a.part_no,b.part_no AS REPLACEMENT_PART_NO
	FROM MyParts a LEFT JOIN MyParts b ON a.part_no=b.REPLACEMENT_PART_NO
	UNION ALL
	SELECT
		c1.part_no,
		b.part_no
	FROM
		cte1 c1
		INNER JOIN MyParts b ON c1.REPLACEMENT_PART_NO = b.REPLACEMENT_PART_NO
)
SELECT DISTINCT
	a.part_no,
	b.Replacements
FROM
	cte1 a
	OUTER APPLY
	(
		SELECT STUFF((
		SELECT ','+b.REPLACEMENT_PART_NO AS [text()]
		FROM cte1 b
		WHERE b.part_no = a.part_no
		ORDER BY b.REPLACEMENT_PART_NO
		FOR XML PATH('')),1,1,'')
	) b(Replacements);

quote:

---

Originally posted by sunitabeck

quote:

---

Originally posted by Furrukh

quote:

---

Originally posted by namman

Furrukh

Do you have solution yet ?

---

no not yet. I am surprise that no one on this forum can answer simple question. but thanks anyways for asking.

---

Everyone who responds to questions on this forum is a volunteer, and answers questions which interests them and have expertise in, in their spare time. So sometimes, questions do fall through the cracks. If you post the query that you have now, even if it does not work correctly, Visakh, or someone else would have given an answer.

In any case, here is one way in which you can use CTE to get the results you are looking for. You will notice that the replacement parts are not ordered in the same order as you showed in your sample results. Not sure if that is sufficient, or if you must have a specific ordering. If you do, it can be done, if you can describe what the rule for ordering is

;WITH cte1 AS
(
	SELECT a.part_no,b.part_no AS REPLACEMENT_PART_NO
	FROM MyParts a LEFT JOIN MyParts b ON a.part_no=b.REPLACEMENT_PART_NO
	UNION ALL
	SELECT
		c1.part_no,
		b.part_no
	FROM
		cte1 c1
		INNER JOIN MyParts b ON c1.REPLACEMENT_PART_NO = b.REPLACEMENT_PART_NO
)
SELECT DISTINCT
	a.part_no,
	b.Replacements
FROM
	cte1 a
	OUTER APPLY
	(
		SELECT STUFF((
		SELECT ','+b.REPLACEMENT_PART_NO AS [text()]
		FROM cte1 b
		WHERE b.part_no = a.part_no
		ORDER BY b.REPLACEMENT_PART_NO
		FOR XML PATH('')),1,1,'')
	) b(Replacements);

BTW, in case you think I am being all negative, I am not!! For example, I thought your first post was great!! - it had the script to create the table and sample data, along with expected results - what more could one ask for!?!

---

quote:

---

Originally posted by Furrukh

quote:

---

Originally posted by namman

Furrukh

Do you have solution yet ?

---

no not yet. I am surprise that no one on this forum can answer simple question. but thanks anyways for asking.

---

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by Furrukh

quote:

---

Originally posted by namman

Furrukh

Do you have solution yet ?

---

no not yet. I am surprise that no one on this forum can answer simple question. but thanks anyways for asking.

---

We can answer as long as we see an attempt from your end. I had given guidelines to you but refrained myself from posting complete solution as I wanted you to try it fast. Giving spoonfed answers will help you only for the particular instance, but you wont learn and benefit anything from that. We want people to learn and understand concept for a solution not just mug up the solution itself.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

quote:

---

Originally posted by visakh16

No problem
I'm not underestimating you (or anyone here).
We get lots of posts here from students who wants to get some solution to complete assignment without doing any try themselves. So unless we see a proper attempt we cant make out whether you're an experienced developer or a student trying to finish an assignment. In any case,posting what you tried would have certainly helped us to understand you better!
Anyways next time you've a problem, please remember to post what you tried so that we can guide you based on that (it need not be your actual query, just sample query with an indication of logic used for a sample data will be sufficient)



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---