Click here to Skip to main content
15,032,967 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Need help on sql query as shown below
I need output column parent item

https://i.stack.imgur.com/1P3TZ.png[^]

What I have tried:

SQL
Select 
T1.ABC, 
T1.Lvl, 
T1.ProductNo,
(Select ProductNo From TableName where Lvl = T1.Lvl - 1) as parantProduct
From TableName T1
Posted
Updated 12-Mar-21 23:07pm
v3
Comments
Member 14936854 13-Mar-21 0:40am
   
Parent item should populate value of product no where lvl =0 for lvl 1 and for lvl 2 it has to populate lvl 1 like wise for all rows
Member 14936854 13-Mar-21 0:43am
   
https://i.stack.imgur.com/1P3TZ.png
OriginalGriff 13-Mar-21 2:08am
   
This is not a good question - we cannot work out from that little what you are trying to do.
Remember that we can't see your screen, access your HDD, or read your mind - we only get exactly what you type to work with. Even with your picture I have no idea what you are trying to do.
And don't post pictures of text: copy and paste the text so we stand a chance of testing with the data rather than having to type it in ...

Use the "Improve question" widget to edit your question and provide better information.
Maciej Los 13-Mar-21 4:08am
   
You need to procide text data rather then image.
What's your input and what output you expect to achieve?

1 solution

My best guess is:

SQL
CREATE TABLE Items
(
  ProductNumber VARCHAR(30),
  ParentItem VARCHAR(30)
)

INSERT INTO Items (ProductNumber, ParentItem)
VALUES('10D68AV', NULL),
('M16503-001', '10D68AV'),
('722979-001', '10D68AV'),
('708984-001', '722979-001'),
('M16499-001', '10D68AV'),
('L47727-001', 'M16499-001')

;WITH Hierarchy AS
(
  -- initial data
  SELECT 0 Lvl, ProductNumber, ParentItem, ROW_NUMBER() OVER(ORDER BY ProductNumber) Rn
  FROM Items
  WHERE ParentItem IS NULL
  -- recursive part
  UNION ALL
  SELECT Lvl +1 , i.ProductNumber, i.ParentItem, ROW_NUMBER() OVER(ORDER BY i.ProductNumber) Rn
  FROM Items i INNER JOIN Hierarchy h ON i.ParentItem = h.ProductNumber
)
SELECT Lvl, ProductNumber, ParentItem
FROM Hierarchy
ORDER BY Lvl, Rn


Result:
Lvl 	ProductNumber 	ParentItem
0 	 	10D68AV 	 	(NULL)
1 	 	722979-001 	 	10D68AV
1 	 	M16499-001 	 	10D68AV
1 	 	M16503-001 	 	10D68AV
2 	 	L47727-001 	 	M16499-001
2 	 	708984-001 	 	722979-001


SQL Server 2019 | db<>fiddle[^]
   
Comments
Member 14936854 13-Mar-21 6:22am
   
Thanks for the query i tried but its not working with my data here is my data

id bom lvl ref
38843 2SZ46AV 0 2SZ46AV
38844 2SZ46AV 1 934253-142
38845 2SZ46AV 1 722979-002
38846 2SZ46AV 2 708984-001
38847 2SZ46AV 1 L16720-001
38848 2SZ46AV 1 L37344-001
38849 2SZ46AV 2 933274-001
38850 2SZ46AV 2 L47720-001
Member 14936854 13-Mar-21 6:26am
   
Here is the out put i need

BOM Lvl ref Parent Item
2SZ46AV 0 2SZ46AV NULL
2SZ46AV 1 934253-142 2SZ46AV
2SZ46AV 1 722979-002 2SZ46AV
2SZ46AV 2 708984-001 722979-002
2SZ46AV 1 L16720-001 2SZ46AV
2SZ46AV 1 L37344-001 2SZ46AV
2SZ46AV 2 933274-001 L37344-001
Maciej Los 13-Mar-21 6:57am
   
You're joking, right?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900