CTE request: how to make them ok
I have a table that stores information about companies and their parent company in the usual hierarchical way, with companyid, parentid and name.
I am just researching a CTE request on Sql Server and writing this request
WITH tableR (ParentCompanyID, CompanyID, Levels)
AS
(
-- Anchor member definition
SELECT e.ParentCompanyID, e.CompanyID, 0 As Levels
FROM tblCompany AS e
WHERE ParentCompanyID in (9)
UNION ALL
-- Recursive member definition
SELECT e.ParentCompanyID, e.CompanyID, Levels + 1
FROM tblCompany AS e
INNER JOIN tableR AS d
ON e.ParentCompanyID = d.CompanyID
)
-- Statement that executes the CTE
SELECT tabler.Levels, tableR.CompanyID, (left('--------------', (tabler.Levels* 2)) + c.CompanyName) as CName,c.ParentCompanyID
FROM tableR Left join tblcompany c on tableR.CompanyId=c.CompanyID
This works great, except that it first displays Child ID = 9, then it enumerates the child level of the first level and then level 2 .. etc., but I need the Child data to be under their parent, so
L0
L1
L2
L1-1
L2-1
....
Can this be done? Because if not, then I have to do it recursively in the C # code I'm using.
I also try to do this
WITH tableR (ParentCompanyID, CompanyID, Levels, RowNumber)
AS
(
-- Anchor member definition
SELECT e.ParentCompanyID, e.CompanyID, 1 As Levels, CAST((Row_Number() Over (Order by e.CompanyName) ) as Varchar(MAx)) as RowNumber
FROM tblCompany AS e
WHERE ParentCompanyID in (9)
UNION ALL
-- Recursive member definition
SELECT e.ParentCompanyID, e.CompanyID, Levels + 1, CAST(Concat(d.RowNumber, CAST((Row_Number() Over (Order by e.CompanyName) ) as VARCHAR(MAX)) ) as VARCHAR(MAX)) as RowNumber
FROM tblCompany AS e
INNER JOIN tableR AS d
ON e.ParentCompanyID = d.CompanyID
)
-- Statement that executes the CTE
SELECT tabler.Levels, RowNumber, tableR.CompanyID, (left('--------------', ((tabler.Levels - 1)* 2 )) + c.CompanyName) as CName,c.ParentCompanyID
FROM tableR Left join tblcompany c on tableR.CompanyId=c.CompanyID order by RowNumber
But this fails if there are more than 9 records on any Level.
a source to share
Try this solution:
DECLARE @Company TABLE
(
CompanyID INT PRIMARY KEY,
Name NVARCHAR(50) NOT NULL,
ParentCompanyID INT NULL
);
INSERT @Company (CompanyID,Name,ParentCompanyID)
VALUES
(8,N'Tomaten',NULL),
(9,N'NON ĂNŞI chars',NULL),
(10,N'Bananen',NULL),
(11,N'Child #1',9),
(12,N'Child #2',9),
(13,N'Child #1.1',11),
(14,N'Child #1.2',11);
DECLARE @ParentCompanyID INT = 9;
WITH RecComp
AS
(
SELECT crt.CompanyID,
crt.Name,
crt.ParentCompanyID,
1 AS Lvl,
N'/' + CONVERT(NVARCHAR(4000),crt.CompanyID) + N'/' AS CompanyNode_AsChar
FROM @Company crt
WHERE crt.ParentCompanyID = @ParentCompanyID
UNION ALL
SELECT cld.CompanyID,
cld.Name,
cld.ParentCompanyID,
prt.Lvl + 1,
prt.CompanyNode_AsChar + CONVERT(NVARCHAR(4000), cld.CompanyID) + N'/'
FROM RecComp prt -- parent
INNER JOIN @Company cld ON prt.CompanyID = cld.ParentCompanyID
)
SELECT *,
CONVERT(HIERARCHYID, CompanyNode_AsChar) AS CompanyNode
FROM RecComp
ORDER BY CompanyNode;
Results:
CompanyID Name ParentCompanyID Lvl CompanyNode_AsChar CompanyNode
--------- ---------- --------------- --- --------------------- -----------
11 Child #1 9 1 /11/ 0xAE
13 Child #1.1 11 2 /11/13/ 0xAF6C
14 Child #1.2 11 2 /11/14/ 0xAF74
12 Child #2 9 1 /12/ 0xB2
a source to share
Well, the ios thing, you don't have an ORDER BY clause.
Why not try at least
-- Statement that executes the CTE
SELECT tabler.Levels, tableR.CompanyID, (left('--------------', (tabler.Levels* 2)) + c.CompanyName) as CName,c.ParentCompanyID
FROM tableR Left join tblcompany c on tableR.CompanyId=c.CompanyID
ORDER BY tableR.Levels
However, the display / user interface should probably be rendered in the output of the user interface, not as you requested.
It's like you want to flatten it in the TreeView.
a source to share
Ok I finally found a release solution. To get critics, and if I'm right to help others, this is
WITH tableR (ParentCompanyID, CompanyID, Levels, RowNumber, RowNumber2)
AS
(
-- Anchor member definition
SELECT e.ParentCompanyID, e.CompanyID, 1 As Levels, CAST((Row_Number() Over (Order by e.CompanyName) ) as Varchar(MAx)) as RowNumber,
CAST(
(Left('000', 3-Len(CAST((Row_Number() Over (Order by e.CompanyName) ) as Varchar(MAx)))) + CAST((Row_Number() Over (Order by e.CompanyName) ) as Varchar(MAx)))
As VARCHAR(MAX)
) AS RowNumber2
FROM tblCompany AS e
WHERE ParentCompanyID in (370)
UNION ALL
-- Recursive member definition
SELECT e.ParentCompanyID, e.CompanyID, Levels + 1, CAST((Row_Number() Over (Order by e.CompanyName) ) as Varchar(MAx)) as RowNumber1,
CAST(
Concat(d.RowNumber2,
Left('000', 3-Len(CAST((Row_Number() Over (Order by e.CompanyName) ) as Varchar(MAx)))),
CAST((Row_Number() Over (Order by e.CompanyName) ) as Varchar(MAx))
) as VARCHAR(MAX)) as RowNumber2
FROM tblCompany AS e
INNER JOIN tableR AS d
ON e.ParentCompanyID = d.CompanyID
)
-- Statement that executes the CTE
SELECT tabler.Levels, RowNumber, RowNumber2, tableR.CompanyID, (left('--------------', ((tabler.Levels - 1)* 2 )) + c.CompanyName) as CName,c.ParentCompanyID
FROM tableR Left join tblcompany c on tableR.CompanyId=c.CompanyID order by RowNumber2, CName
Now here is the explanation:
- I add the SQL Server ROW_NUMBER function, this just adds a counter to each query row, so it adds a separate counter to the Anchor query and recursive query.
- But since we have to reorder them to add the parent / anchor request value to Child, so the Anchor request goes 1, 2, 3 .. but the child goes 11, 12 ... 21 ...
- Then I will pass them as String because in String Order you will have 1, 2, 21, 3 and not 1, 2, 3, 21 .. so this works great for me.
The issue is Known: It hits the game when you delete the output of an anchor query> 10 rows, or click on any row more than 10 rows, since then the anchor query gives id 11 and the child query 111 and confuses the result.
Solution for above problem: I change my query to use LEFT and append 000, so if I see I can max out 100 children, I put 3 zeros, if you see 4, then use 0000 and change from 3 to 4 in the query ...
BUT: I highly recommend the answer above as this is the way to do it.
a source to share
I would like to share this
if you want to order data ... literal and child data comes right under the parents .. create baseCTE, use row_number instead of CompanyID, call Anchor request from Base CTE
BASE CTE
ROW_NUMBER() OVER ( PARTITION BY ParentCompanyID ORDER BY CompanyName) as [row_number]
Anchor request
N'/' + CONVERT(NVARCHAR(4000),[row_number]) + N'/' AS CompanyNode_AsChar
Recursive query
prt.CompanyNode_AsChar + CONVERT(NVARCHAR(4000), [row_number]) + N'/'
a source to share