July 29, 2008 at 7:52 am
Hi all,
I've two tables
Table -A
ItemId SubItem
Item1 Item A
Item2 Item B
Item3 Item C
Item4 Item D
Item5 Item E
Table -B
ParentItem ChildItem
Item A Item aa
Item B Item bb
Item C Item cc
Item D Item dd
Item E Item aa
Item aa Item ee
Item ee Item ff
Item bb Item gg
Visualize this as a parent-child data, meaning a childitem can inturn be a parentitem (Item aa, item ee, item bb are in both column).
I need the output in the following format -
Item 1 Item A
Item 1 Item aa
Item 1 Item ee
Item 1 Item ff
Item 2 ItemB
Item 2 Item bb
Item 2 Item gg
Item3 Item C
Item3 Item cc
Item4 Item D
Item4 Item dd
Item5 Item E
Item5 Item aa
Item5 Item ee
Item5 Item ff
Any suggestions... I dont want to create a temporary table. The challenge is I cannot write a SP 'coz we are fetching the data from another server for which we have readonly permission. Can this be written in a simple way?
Thanks
July 29, 2008 at 2:19 pm
Yes, this can be done relatively easily. Build a recursive CTE for the second table (Books Online has an article about it under "Common Table Expressions"), and you should end up with what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 30, 2008 at 9:31 am
Can you pl. provide me sample code?
July 30, 2008 at 10:15 am
;with Hierarchy (ItemID, Item, Parent) as
(select ItemID, SubItem, null
from Table1
union all
select hierarchy.itemid, table2.childitem, table2.parentitem
from table2
inner join hierarchy
on table2.parentitem = hierarchy.item)
select *
from hierarchy
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply