Improve company productivity with a Business Account.Sign Up

x
?
Solved

SQL - Store procedure

Posted on 2013-11-28
3
Medium Priority
?
604 Views
Last Modified: 2013-12-02
Dear Expert

I would like to build a SP to product the result from Source to the Report as follows:

Source:

Parent     Child
F00000     W1099999
F00000     RPL12345
W1099999   RPL88888
RPL88888   KKK44444



Report:

Parent     Child      Level   Hierarchy
F00000     W1099999   1       F00000.W1099999
F00000     RPL12345   1       F00000.RPL12345
W1099999   RPL88888   2       F00000.W1099999.RPL88888  
RPL88888   KKK44444   3       F00000.W1099999.RPL88888.KKK44444



That's all

Thanks so much
0
Comment
Question by:beckyng
  • 2
3 Comments
 
LVL 4

Accepted Solution

by:
Hiran Desai earned 900 total points
ID: 39683280
It seems to have I can be done with Common Table Expression(CTE).

Create table #temp
(
      parent nvarchar(100),
      child nvarchar(100)
)

insert #temp values('F00000','W1099999')
insert #temp values('F00000','RPL12345')
insert #temp values('W1099999','RPL88888')
insert #temp values('RPL88888','KKK44444');

with myResult (parent,child,increment)
as
(
      Select *,1 as depth
      from #temp In1Temp
      where In1Temp.parent not in
      (
            Select In2Temp.child
            from #temp In2Temp
      )
      union all
      Select outTemp.*,r.increment+1
      from #temp outTemp
      inner join myResult r
      on outTemp.parent=r.child
      
)
Select *
from myResult;

drop table #temp;

Hope this what you where looking for :)
0
 

Author Comment

by:beckyng
ID: 39683456
HI expert

Thanks for your quick reply.

May I ask the remaing field of "Hierarchy"?

Thanks
0
 
LVL 4

Expert Comment

by:Hiran Desai
ID: 39691617
Oh! sorry forgot to add remaining part..:(
but here is your updated solution...

Create table #temp
(
      parent nvarchar(100),
      child nvarchar(100)
)

insert #temp values('F00000','W1099999')
insert #temp values('F00000','RPL12345')
insert #temp values('W1099999','RPL88888')
insert #temp values('RPL88888','KKK44444');

with myResult (parent,child,increment,Hierarchy)
as
(
      Select *,1 as depth,convert(nvarchar(max),(parent+'.'+child))
      from #temp In1Temp
      where In1Temp.parent not in
      (
            Select In2Temp.child
            from #temp In2Temp
      )
      union all
      Select outTemp.*,r.increment+1,r.Hierarchy+'.'+outTemp.child
      from #temp outTemp
      inner join myResult r
      on outTemp.parent=r.child
     
)
Select *
from myResult;

drop table #temp;
0

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

608 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question