SQL Server Tree Query

Question

I need some help is MS SQL Server Query. I’m not much of a DBA. I have an application with an Organization Table which is made up of a parent-child relationship:

CREATE TABLE [dbo].[Organizations](
    [OrgPK] [int] IDENTITY(1,1) NOT NULL,
    [OrgParentFK] [int] NULL,
    [OrgName] [varchar](200) NOT NULL,
CONSTRAINT [PK__Organizations] PRIMARY KEY CLUSTERED

Sample data looks like this:

OrgPK,   OrgParentFK, OrgName
1,  0,  Corporate
2,  1,  Department A
3,  1,  Department B
4,  2,  Division 1
5,  2,  Division 2
6,  3,  Division 1
7,  6,  Section 1
8,  6,  Section 2

I'm trying to generate a query that returns an org path based on a given OrgPK. Example if given OrgPK = 7 the query would return 'Corporation/Department B/Division 1/Section 1'

If give OrgPk = 5 the return string would be 'Corporation/Department A/Division 2'

Thank you for your assistance.

Do you have a foreign key constraint on OrgParentFK?

The Scrum Meister
– The Scrum Meister

2011-02-17 20:41:20 +00:00
Commented Feb 17, 2011 at 20:41 — The Scrum Meister
– The Scrum Meister, Commented Feb 17, 2011 at 20:41

The Scrum Meister · Accepted Answer · 2011-02-17 21:30:22Z

1

WITH  OrganizationsH (OrgParentFK, OrgPK, OrgName, level, Label) AS
(
    SELECT OrgParentFK, OrgPK, OrgName, 0, CAST(OrgName AS VARCHAR(MAX)) As Label
    FROM Organizations
    WHERE OrgParentFK IS NULL
    UNION ALL
    SELECT o.OrgParentFK, o.OrgPK, o.OrgName, level + 1,  CAST(h.Label + '/' + o.OrgName  VARCHAR(MAX)) As Label
    FROM Organizations o JOIN OrganizationsH h ON o.OrgParentFK = h.OrgPK
)

SELECT OrgParentFK, OrgPK, OrgName, level, Label
FROM OrganizationsH
WHERE OrgPK = 5

h/t to marc_s

edited Feb 17, 2011 at 21:30

answered Feb 17, 2011 at 20:48

The Scrum Meister

30.2k8 gold badges69 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

marc_s Over a year ago

You'll probably have to put CAST(..... AS VARCHAR(MAX)) around your "Label" entries (otherwise you'll get: "Msg 240, Level 16, State 1, Line 11 - Types don't match between the anchor and the recursive part in column "Label" of recursive query "OrganizationsH"." - but other than that: darn, beat me to it!! :-)

The Scrum Meister Over a year ago

@marc-s interesting, i wasn't aware of that. Is the CAST needed in both places?

pcofre · Accepted Answer · 2011-02-17 21:21:51Z

0

It can also be solved by creating a scalar valued function:

-- SELECT [dbo].[ListTree](5)
CREATE FUNCTION [dbo].[ListTree](@OrgPK int)
RETURNS varchar(max)
AS
BEGIN
    declare @Tree varchar(MAX)
    set @Tree = ''

    while(exists(select * from dbo.Organizations where OrgPK=@OrgPK))
    begin
        select  @Tree=OrgName+'/'+@Tree,
                @OrgPK=OrgParentFK
        from    dbo.Organizations
        where   OrgPK=@OrgPK

    end
    return  left(@Tree,len(@Tree)-1)
END

answered Feb 17, 2011 at 21:21

pcofre

4,07420 silver badges27 bronze badges

Collectives™ on Stack Overflow

SQL Server Tree Query

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related