Representation Of Parent Child Relation

Question

 

Hi All ,

Am Stuck in a situation kindly help

Column1	Column2	Column3	Column4
OrderItid	orderid	Item	RelatedID
23	33	hard drive 500gb
24	33	additional hard drive 100gb	23
25	33	cable set	23
26	33	usb
27	33	mmc	26
28	33	2 mmc
29	34	cd drive	28

as per table above all have the same common orderid that is 33 . So the case is like the orderid is made up of several other components

like here in 1 orderid there are  orderidcomponents. Lets take the orderitcomponent as the parent id and relateditemid as childid.

Both columns Orderitemidcomponent and RelatedItemid contains unique values. So if u as a customer bought a hard drive of 500gb and want

a new item with that like here it is additional hard drive 100 gb and cable set  under the hard drive of 500gb .Orderid is 33 not 34

 i want a parent Child Relationship in the Format Below :

Main-SubItem	Item
1	hard drive 500gb
1.1	additional hard drive 100gb
1.2	cable set
2	usb
2.1	mmc
3	2 mmc
3.1	cd drive

 

Any other kind of representation with the help of which i can easily distinguish the parent and child relationship will also do. The attributes remain the same but .Kindly Help

Thanks in advance

Answer 1

Normally you would use a recursive CTE for that, but in your case a simple ROW_NUMBER() and DENSE_RANK() is sufficient:

 

WITH Numbered AS
(
  SELECT *,
    DENSE_RANK() OVER ( PARTITION BY OrderID ORDER BY COALESCE(RelatedItemID, OrderItemIDComponent ) AS MainItemNo,
    ROW_NUMBER() OVER ( PARTITION BY OrderID, COALESCE(RelatedItemID, OrderItemIDComponent ORDER BY OrderItemIDComponent ) - 1 AS SubItemNo
  FROM yourTable
)
  SELECT *,
    CAST(MainItemNo AS VARCHAR(8)) + 
    CASE WHEN SubItemNo = 0 THEN ''
      ELSE '.' + CAST(SubItemNo AS VARCHAR(8))
    END AS ItemNo
  FROM Numbered
  ORDER BY OrderID, MainItemNo, SubItemNo;

Answer 2

Hi Stefan ,

Out put of ur query is

but required output is

orderitid	item	orderid	relation	relatedtid	mainitemno	subitemno	itemno
26	harddrive	33	0	1	0	1
27	cd	33	0	1	1	1.1
28	usb	33	0	1	2	1.2
29	lcd	33	0	1	3	1.3
30	light pen	33	0	1	4	1.4
31	storage	33	0	1	5	1.5
32	mmc	33	0	1	6	1.6
33	mmc *2	33	0	1	7	1.7
34	powercable	33	PARENT	26	2	8	2.8
35	cable	33	PARENT	26	2	9	2.9
36	fan	33	PARENT	29	3	10	3.1
37	mouse	33	PARENT	27	4	11	4.11
38	printer	33	PARENT	31	4	12	4.12
39	scanner	33	PARENT	31	5	13	5.13
40	router	33	PARENT	32	5	14	5.14
41	lan cable	33	PARENT	30	6	15	6.15
42	disk reader	33	PARENT	30	6	16	6.16

  this kind of output is required thanks for the revert

item/subitem no	item
1	harddrive
1.1	powercable
1.2	cable
2	cd
2.1	mouse
3	usb
4	lcd
4.1	fan
5	light pen
5.1	lan cable
5.2	disk reader
6	storage
6.1	scanner
7	mmc
7.1	router
8	mmc*2

Answer 3

Can you post CREATE TABLE and INSERT INTO-s so we can test as well?  Thanks.

---

Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 4

yes sure :)

below r the create table and insert for it

create table test
( orderitemcompoenentid integer primary key ,
  item varchar(30),
  orderid integer,
  relation varchar(10),
  relatedorderitemcomponentid integer)
 
  insert into test values(26,'harddrive',33,0,0)
  insert into test values(27,'cd',33,0,0)
  insert into test values(28,'usb',33,0,0)
  insert into test values(29,'lcd',33,0,0)
  insert into test values(30,'lightpen',33,0,0)
  insert into test values(31,'Storage',33,0,0)
  insert into test values(32,'MMC',33,0,0)
  insert into test values(33,'MMC*2',33,0,0)
  insert into test values(34,'powercable',33,'parent',26)
  insert into test values(35,'cable',33,'parent',26)
  insert into test values(36,'fan',33,'parent',29)
  insert into test values(37,'mouse',33,'parent',27)
  insert into test values(38,'printer',33,'parent',31)
  insert into test values(39,'scanner',33,'parent',31)
  insert into test values(40,'router',33,'parent',32)
  insert into test values(41,'lancable',33,'parent',30)
  insert into test values(42,'diskreader',33,'parent',30)

Answer 5

Try this, without DENSE_RANK():

WITH MainNumbered AS
	(
		SELECT	*,
				ROW_NUMBER() OVER ( PARTITION BY OrderID ORDER BY orderitemcompoenentid ) AS MainItemNo,
				0 AS SubItemNo
		FROM	test
		WHERE	relatedorderitemcomponentid = 0
	),
	SubNumbered AS
	(
		SELECT	T.*,
				M.MainItemNo, 
				ROW_NUMBER() OVER ( PARTITION BY T.OrderID, T.relatedorderitemcomponentid ORDER BY T.relatedorderitemcomponentid ) AS SubItemNo
		FROM	test T
			INNER JOIN MainNumbered M ON M.orderid = T.orderid AND M.orderitemcompoenentid = T.relatedorderitemcomponentid
		WHERE T.relatedorderitemcomponentid <> 0
	)
	SELECT	*
	FROM	MainNumbered
	UNION ALL
	SELECT	*
	FROM	SubNumbered
    ORDER BY OrderID, MainItemNo, SubItemNo;

Answer 6

Hi Stefan ,

Thanks a ton .The Results are coming fine from query only concern now left is that can't we have output that i suggested above that was

like

Item-SubItem no

1                                             Parent

1.1                                           child

1.2                                           child

Answer 7

Try:

   WITH MainNumbered AS
	(
		SELECT	*,
				ROW_NUMBER() OVER ( PARTITION BY OrderID ORDER BY orderitemcompoenentid ) AS MainItemNo,
				0 AS SubItemNo
		FROM	test
		WHERE	relatedorderitemcomponentid = 0
	),
	SubNumbered AS
	(
		SELECT	T.*,
				M.MainItemNo, 
				ROW_NUMBER() OVER ( PARTITION BY T.OrderID, T.relatedorderitemcomponentid ORDER BY T.relatedorderitemcomponentid ) AS SubItemNo
		FROM	test T
			INNER JOIN MainNumbered M ON M.orderid = T.orderid AND M.orderitemcompoenentid = T.relatedorderitemcomponentid
		WHERE T.relatedorderitemcomponentid <> 0
	),
	CTE AS (
	SELECT	*
	FROM	MainNumbered
	UNION ALL
	SELECT	*
	FROM	SubNumbered)

	SELECT IndexNo = CASE WHEN SubItemNo != 0 THEN CONCAT(convert(varchar,MainItemNo),'.',
	                        convert(varchar,SubItemNo)) 
						  ELSE convert(varchar,MainItemNo)END , *
	FROM CTE
	    ORDER BY OrderID, MainItemNo, SubItemNo;
/*
	IndexNo	orderitemcompoenentid	item	orderid
1	26	harddrive	33
1.1	34	powercable	33
1.2	35	cable	33
2	27	cd	33
2.1	37	mouse	33
3	28	usb	33
4	29	lcd	33
4.1	36	fan	33
...
*/

---

Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 8

Hi Kalman ,

Really thanks a ton it works flawlessly .IF in future there is GrandParent - Parent - Child relationship comes up will it work the same way .

Kind Regards

Priya

Answer 9

Hi Kalman ,

Really thanks a ton it works flawlessly .IF in future there is GrandParent - Parent - Child relationship comes up will it work the same way .

Kind Regards

Priya

The likelihood is good that it can be expanded to another hierarchy level.

For additional question(s) open new thread(s).

---

Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 10

Hi Kalman ,

Thanks for the revert but recently a scenario like this came up and its output as per the query u mentioned above the output was like this :

index	orderitemcomponentid	item	orderid	relation	relatedorderiditemcomponentid
1	65	AC	15	0
1.1	66	Fridge	15	parent	65
null	67	Cooker	15	parent	66
null	68	Harddrive	15	parent	67

fig: the zero is above the 65 no

 so for orderitemcomponentiditem 67 and 68 the index was null whereas it should be something like 1.1.1 etc . I hope u got the scenerio . any other kind of output will also do till the time it makes that clear that who's the parent item child item and respeectively the sub child .

Thanks a ton for support ..

Kind Regards

Answer 11

With what test data? Please include concise and complete scripts in every post which touches a new problem..

Answer 12

Hi Stefan !

Sorry for the incomplete information . The Create table script i have already mentioned in  above Post whereas for this  case the inserts are here

below

insert into test values(65,'AC',15,'',0)
insert into test values(66,'Fridge',15,'parent',65)
insert into test values(67,'Cooker',15,'parent',66)
insert into test values(68,'HardDrive',15,'parent',67)

I need the complete parent,child,grandchild relationship in a column named item-subitem and it needs to be displayed in numeric pattern or any other

suitable by which 1 can easily identify the relationship .Please help

Thanks in Advance

Answer 13

Hi Priya ,

Try thz it will work

WITH OrderItemsCTE AS (
SELECT orderitemcompoenentid, relatedOrderItemComponentId, cast(ROW_NUMBER() OVER(Order by relatedorderitemcomponentid) as varchar(20)) as rowNum
FROM test
WHERE relatedOrderItemComponentId =0

UNION ALL
SELECT x.orderitemcompoenentid, x.relatedorderitemcomponentid, cast(octe.rowNum + '.' + cast(ROW_NUMBER() OVER(Order by x.relatedorderitemcomponentid) as varchar(5)) as varchar(20)) as rowNum
FROM test x
INNER JOIN OrderItemsCTE octe ON octe.orderitemcompoenentid = x.relatedorderitemcomponentid

Note:If it works please mark it as Answer

Answer 14

Hi ,

Thanks a ton it works this is what i was looking for .