Thomas Colbert Thomas Colbert - 2 months ago 6
SQL Question

Display child rows under parent rows in SQL Server Stored Procedure

There is a table that has information about projects stored in it. Each Project has an ID Column(FPNNumber) and a Parent ID column. If the project has a parent project, the user selects the ID number of the parent project to go in the parent project cell for that row. If it doesn't have a parent project, the parent project column will have a 0. I've seen some questions on here similar to mine but it looks like they are all using a level column which I don't have or want to use in my query.

My stored for getting the data from the database looks like this:

SELECT h.[Priority]
,h.[FPNNumber]
,h.[ProjectName]
,sp.SponsorName
,pm.ProjectManagerName
,o.[OrgName]
,HealthID
,[StrategicAlignmentID] + [FinancialAlignmentID] + [TechnologyAlignmentID] As ProjectScore
,h.[Cost]
,h.[Budget]
,h.[PercentComplete]
,Convert(varchar(10),h.[EstimatedImpDt], 101) As EstimatedImpDt
,[EstimatedROI]
,h.[ExpectedBenefit]
,CONVERT(Decimal(10, 2),((DATEDIFF(day, h.[PaybackPerioddt], GETDATE()))/365.0)) As PaybackPerioddt

FROM [ProjectNew].[Header] h
join ProjectNew.Sponsor sp ON h.SponsorID = sp.SponsorID
join ProjectNew.ProjectManager pm ON h.ProjectManagerID = pm.ProjectManagerID
Join ProjectNew.Organization o ON h.OrgID = o.OrgID
where StatusID is null or StatusID < 12


One thing I've tried and worked partially but doesn't work quite how I need was

;WITH Parents
AS(
SELECT h.[Priority]
,h.[FPNNumber]
,h.[ProjectName]
,sp.SponsorName
,pm.ProjectManagerName
,o.[OrgName]
,HealthID
,[StrategicAlignmentID] + [FinancialAlignmentID] + [TechnologyAlignmentID] As ProjectScore
,h.[Cost]
,h.[Budget]
,h.[PercentComplete]
,Convert(varchar(10),h.[EstimatedImpDt], 101) As EstimatedImpDt
,[EstimatedROI]
,h.[ExpectedBenefit]
,CONVERT(Decimal(10, 2),((DATEDIFF(day, h.[PaybackPerioddt], GETDATE()))/365.0)) As PaybackPerioddt

FROM [FPN].[ProjectNew].[Header] h
join ProjectNew.Sponsor sp ON h.SponsorID = sp.SponsorID
join ProjectNew.ProjectManager pm ON h.ProjectManagerID = pm.ProjectManagerID
Join ProjectNew.Organization o ON h.OrgID = o.OrgID
WHERE StatusID is null or StatusID < 12 and h.ParentID = 0
Union All
SELECT he.[Priority]
,he.[FPNNumber]
,he.[ProjectName]
,sp.SponsorName
,pm.ProjectManagerName
,o.[OrgName]
,he.HealthID
,[StrategicAlignmentID] + [FinancialAlignmentID] + [TechnologyAlignmentID] As ProjectScore
,he.[Cost]
,he.[Budget]
,he.[PercentComplete]
,Convert(varchar(10),he.[EstimatedImpDt], 101) As EstimatedImpDt
,he.[EstimatedROI]
,he.[ExpectedBenefit]
,CONVERT(Decimal(10, 2),((DATEDIFF(day, he.[PaybackPerioddt], GETDATE()))/365.0)) As PaybackPerioddt

FROM [FPN].[ProjectNew].[Header] he
join ProjectNew.Sponsor sp ON he.SponsorID = sp.SponsorID
join ProjectNew.ProjectManager pm ON he.ProjectManagerID = pm.ProjectManagerID
Join ProjectNew.Organization o ON he.OrgID = o.OrgID
Join Parents cte on cte.FPNNumber = he.ParentID
WHERE StatusID is null or StatusID < 12
)
Select * from Parents


When I execute that, the results look like this:

ID | ParentID
----------------
1 | 0
2 | 0
4 | 2
5 | 2
3 | 1
6 | 1
7 | 1


I need it to look like this:

ID | ParentID
----------------
1 | 0
3 | 1
6 | 1
7 | 1
2 | 0
4 | 2
5 | 2


So for the row with ID 2 has the two corresponding child projects under it, but the Child Projects of ID 1 are below that.

What am I doing wrong? Is there a way to get all child projects to display under their parent project? If so, Is there a way to do this, and then when I call that data to the ListView in Visual Studio, have it so if the user clicks a sort button, it doesn't scramble up the child projects?

Answer
Select * from Parents
ORDER BY
    CASE WHEN ParentId = 0 THEN Id ELSE ParentId END
    ,ID

I don't want to try and troubleshoot your recursive code and you are saying it is returning the expected results just not in the expected order. So add an order by statement. To get to the order you want you can test if ParentId = 0 and if it is swap it's own id in which will create a grouping of all of the items. Then order by ID which will put the ParentId First assuming parentid is always less than childs (if it is possible to have a parentid > childid you can add ParentId between the case and ID and it should work.

During recursion sql-server doesn't care about order it optimizes based on indexes etc. If you want to see how it steps through the recursion you can add a 0 AS LEVEL to the anchor and a Level + 1 to the recursive statement and it will show you at which iteration each child is added.

Comments