Need help on SQL on populating previous value of a column

Question

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 12-Mar-21 18:37pm

Member 14936854

Updated 12-Mar-21 23:07pm

Maciej Los

v3

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Answer 1 · 2021-03-12T23:07:00

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[^]