Parent and Child Data Grouping

Question

Parent and Child Data Grouping

flash.rsn

SSC-Addicted

Points: 437
More actions
December 16, 2009 at 3:02 am

#213605

Dear All
I have item data, the each item identified as master or child item, the flag (Itemtype) has maintained for parent and child. The data has following.
Table:1
IndexItem NumberItemType
1ITM00000011
2ITM00000102
3ITM00000992
4ITM00000111
5ITM00000881
6ITM00000771
7ITM00000662
8ITM00000162
9ITM00000141
10ITM00000392
Note: each item in table either master or child, if the item is child (itemType=2) must have parent, if the item is parent(itemtype=1) must have child, the child and master information is exists in following seprate table.
Table:1
Master Child
ITM0000001ITM0000010
ITM0000011ITM0000099
ITM0000088ITM0000066
ITM0000077ITM0000016
ITM0000014ITM0000039
Requirement: when I use select statement, the result must have unique number for each parent and child set for grouping purpose on report, I want pair of child and parent in separate group.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

What do you want your output to look like, using the sample data you've provided?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

flash.rsn SSC-Addicted Points: 437 More actions · Answer 2

very simple, when i execute select statement it should return all items with identification of child's master item or master's child. Actually my goal is to findout that if the item is child then what is it's parent or if the item is parent what is it's child item. According to me, this goal could be achive by using RANK() ,DENSE_RANK() etc.

Waiting for your suggestion / assistance.