http://pastebin.com/1ZSKASZa#
CREATE TABLE dbo.company
(companyid INT NOT NULL PRIMARY KEY,
parentcompanyid INT NULL,
companyname VARCHAR(25) NOT NULL)
INSERT company (companyid, parentcompanyid, companyname)
VALUES (1, NULL, 'AAA-Corp')
INSERT company (companyid, parentcompanyid, companyname)
VALUES (2, 1, 'BBB-Corp')
INSERT company (companyid, parentcompanyid, companyname)
VALUES (3, 1, 'CCC-Corp')
INSERT company (companyid, parentcompanyid, companyname)
VALUES (4, 3, 'DDD-Corp')
INSERT company (companyid, parentcompanyid, companyname)
VALUES (5, 4, 'EEE-Corp')
INSERT company (companyid, parentcompanyid, companyname)
VALUES (6, 5, 'FFF-Corp')
INSERT company (companyid, parentcompanyid, companyname)
VALUES (7, 5, 'GGG-Corp')
SELECT * FROM company
WITH companyStructure(Parent, CompanyID, CompanyName, CompanyLevel)
AS(
SELECT parentcompanyid
,companyid
,companyname
,0 AS CompanyLevel
FROM company
WHERE parentcompanyid IS NULL
UNION ALL
SELECT c.parentcompanyid
,c.companyid
,c.companyname
,s.CompanyLevel + 1
FROM company c
JOIN companyStructure s ON c.parentcompanyid = s.companyid
)
SELECT Parent, CompanyID, CompanyName, CompanyLevel
FROM companyStructure
--防止無線迴圈
OPTION (MAXRECURSION 10)