September 10, 2012 at 7:58 am
Good morning fellas, i have a unique problem that needs a unique solution,
i have an inventory list that stores business_unit, parent_item_no, and child_item_no; child_item_no can also be parents to other child_item_no but still will be listed as child_item_no, irrespective of their parents. am trying to write a query to produce the business_unit, parent_item_no, the level to which the child_item_no relates to the parent, and the child_item_no, i have attached an excel sheet to show how i would want the product of the script to look like, any help is appreciated. thanks guys.
September 10, 2012 at 8:05 am
Look up Common Table Expressions in Books Online. That's precisely what it was created for.
http://msdn.microsoft.com/en-us/library/ms190766(v=SQL.105).aspx
September 10, 2012 at 8:14 am
yes, i have but i but i can't seem to write the anchor statement properly
September 10, 2012 at 8:31 am
Post your table DLL, an INSERT statement with sample data, and what you've written on the CTE. That will help us help you better.
September 10, 2012 at 8:50 am
I'll take a shot in the dark, from what I can see in the data you have posted there are no levels, 235021 is the only parent so all levels returned will be one, however I think you want something like this (I've added an extra row of data)
DECLARE @TABLE AS TABLE
(BusinessUnit CHAR(3),ParentItemShortNo INT,ChildItemShortNo INT)
INSERT INTO @TABLE
SELECT 'ACD',235021,235277 UNION ALL
SELECT 'ACD',235021,235281 UNION ALL
SELECT 'ACD',235021,235292 UNION ALL
SELECT 'ACD',235021,235297 UNION ALL
SELECT 'ACD',235021,235298 UNION ALL
SELECT 'ACD',235021,235302 UNION ALL
SELECT 'ACD',235021,235304 UNION ALL
SELECT 'ACD',235021,235320 UNION ALL
SELECT 'ACD',235021,235362 UNION ALL
SELECT 'ACD',235021,235377 UNION ALL
SELECT 'ACD',235021,235385 UNION ALL
SELECT 'ACD',235021,235388 UNION ALL
SELECT 'ACD',235021,235392 UNION ALL
SELECT 'ACD',235021,235531 UNION ALL
SELECT 'ACD',235021,235575 UNION ALL
SELECT 'ACD',235021,235836 UNION ALL
SELECT 'ACD',235021,257885 UNION ALL
SELECT 'ACD',257885,99 UNION ALL
SELECT 'ACD',235021,322618
;
WITH CTE (BusinessUnit, ParentItemShortNo, ChildItemShortNo, Lv)
AS
(
SELECT
BusinessUnit
,ParentItemShortNo
,ChildItemShortNo
,1 AS lv
FROM
@TABLE
UNION ALL
SELECT
CTE.BusinessUnit
,CTE.ParentItemShortNo
,T.ChildItemShortNo
,CTE.lv + 1
FROM
@TABLE AS T
INNER JOIN CTE
ON T.ParentItemShortNo = CTE.ChildItemShortNo
)
SELECT *
FROM CTE
However without a little more to go on its difficult to know
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
September 10, 2012 at 8:55 am
that's a great example, thanks, i wanted to add that the the select statement is for just one business_unit and one parent_item_no, there are multiple business_units and parent_item_no's, so based on your solution what would be the way to go about that?.
September 10, 2012 at 9:08 am
It would be easier for us to answer that question if you posted the requested DDL and sample data. The sample data doesn't have to be real. You can fake it. We just need something as a reference point.
September 10, 2012 at 9:16 am
Thanks 🙂
that's a great example, thanks, i wanted to add that the the select statement is for just one business_unit and one parent_item_no, there are multiple business_units and parent_item_no's, so based on your solution what would be the way to go about that?.
Not 100% sure what you mean, if you want to restrict the results to one business unit just pop that in a WHERE clause in the first part of the CTE
Brandie is right, it would help to have some examples..
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
September 10, 2012 at 9:19 am
thank you, but i don't want to restrict the results to one business unit, i want to include all business units
September 10, 2012 at 9:22 am
want to include all business units
The code I posted above for you will run for all business units as it is, there is no restriction
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
September 10, 2012 at 9:25 am
thank you,i appreciate all your help
September 10, 2012 at 9:30 am
No Worries
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply