Mixing
TSQL Manager Hierarchy
I need to be able to show all my managers in a hierarchy in different columns. I don't know how many levels there will be.
Example: Employee – ManagerOfEmployee - TheBigBoss
I have tried the below but cant get it to work the way I want it.
I need the results to look like this:
Level1Column Level2Column Level3Column
------------------------------------------
1 2 3
Code:
CREATE TABLE #tblHRData
(
Emplid INT,
ReportsToEmplid INT
)
INSERT INTO #tblHRData (Emplid, ReportsToEmplid)
VALUES (1, 2), (2, 3)
;WITH CTE AS
(
SELECT
Emplid,
ReportsToEmplid,
1 AS level
FROM
#tblHRData
WHERE
Emplid = 1
UNION ALL
SELECT
child.Emplid,
child.ReportsToEmplid,
level + 1
FROM
#tblHRData child
JOIN
CTE parent ON child.ReportsToEmplid = parent.Emplid
)
SELECT *
FROM CTE;
sql-server database tsql common-table-expression hierarchy edited Nov 20 '18 at 5:39
marc_s
572k12811071254
asked Nov 20 '18 at 3:29
EtienneEtienne
2,9773894140
add a comment |
I need to be able to show all my managers in a hierarchy in different columns. I don't know how many levels there will be.
Example: Employee – ManagerOfEmployee - TheBigBoss
I have tried the below but cant get it to work the way I want it.
I need the results to look like this:
Level1Column Level2Column Level3Column
------------------------------------------
1 2 3
Code:
CREATE TABLE #tblHRData
(
Emplid INT,
ReportsToEmplid INT
)
INSERT INTO #tblHRData (Emplid, ReportsToEmplid)
VALUES (1, 2), (2, 3)
;WITH CTE AS
(
SELECT
Emplid,
ReportsToEmplid,
1 AS level
FROM
#tblHRData
WHERE
Emplid = 1
UNION ALL
SELECT
child.Emplid,
child.ReportsToEmplid,
level + 1
FROM
#tblHRData child
JOIN
CTE parent ON child.ReportsToEmplid = parent.Emplid
)
SELECT *
FROM CTE;
edited Nov 20 '18 at 5:39
marc_s
572k12811071254
asked Nov 20 '18 at 3:29
EtienneEtienne
2,9773894140
You need a dynamic pivot.
– Zohar Peled
Nov 20 '18 at 4:45
i think, this should be done in presentation layer...
– Abdul Rasheed
Nov 20 '18 at 4:56
add a comment |
I need to be able to show all my managers in a hierarchy in different columns. I don't know how many levels there will be.
Example: Employee – ManagerOfEmployee - TheBigBoss
I have tried the below but cant get it to work the way I want it.
I need the results to look like this:
Level1Column Level2Column Level3Column
------------------------------------------
1 2 3
Code:
CREATE TABLE #tblHRData
(
Emplid INT,
ReportsToEmplid INT
)
INSERT INTO #tblHRData (Emplid, ReportsToEmplid)
VALUES (1, 2), (2, 3)
;WITH CTE AS
(
SELECT
Emplid,
ReportsToEmplid,
1 AS level
FROM
#tblHRData
WHERE
Emplid = 1
UNION ALL
SELECT
child.Emplid,
child.ReportsToEmplid,
level + 1
FROM
#tblHRData child
JOIN
CTE parent ON child.ReportsToEmplid = parent.Emplid
)
SELECT *
FROM CTE;
edited Nov 20 '18 at 5:39
marc_s
572k12811071254
asked Nov 20 '18 at 3:29
EtienneEtienne
2,9773894140
I need to be able to show all my managers in a hierarchy in different columns. I don't know how many levels there will be.
Example: Employee – ManagerOfEmployee - TheBigBoss
I have tried the below but cant get it to work the way I want it.
I need the results to look like this:
Level1Column Level2Column Level3Column
------------------------------------------
1 2 3
Code:
CREATE TABLE #tblHRData
(
Emplid INT,
ReportsToEmplid INT
)
INSERT INTO #tblHRData (Emplid, ReportsToEmplid)
VALUES (1, 2), (2, 3)
;WITH CTE AS
(
SELECT
Emplid,
ReportsToEmplid,
1 AS level
FROM
#tblHRData
WHERE
Emplid = 1
UNION ALL
SELECT
child.Emplid,
child.ReportsToEmplid,
level + 1
FROM
#tblHRData child
JOIN
CTE parent ON child.ReportsToEmplid = parent.Emplid
)
SELECT *
FROM CTE;
edited Nov 20 '18 at 5:39
marc_s
572k12811071254
asked Nov 20 '18 at 3:29
EtienneEtienne
2,9773894140
edited Nov 20 '18 at 5:39
marc_s
572k12811071254
asked Nov 20 '18 at 3:29
EtienneEtienne
2,9773894140
edited Nov 20 '18 at 5:39
marc_s
572k12811071254
edited Nov 20 '18 at 5:39
marc_s
572k12811071254
edited Nov 20 '18 at 5:39
marc_s
572k12811071254
572k12811071254
asked Nov 20 '18 at 3:29
EtienneEtienne
2,9773894140
asked Nov 20 '18 at 3:29
EtienneEtienne
2,9773894140
asked Nov 20 '18 at 3:29
EtienneEtienne
2,9773894140
2,9773894140
You need a dynamic pivot.
– Zohar Peled
Nov 20 '18 at 4:45
i think, this should be done in presentation layer...
– Abdul Rasheed
Nov 20 '18 at 4:56
add a comment |
You need a dynamic pivot.
– Zohar Peled
Nov 20 '18 at 4:45
i think, this should be done in presentation layer...
– Abdul Rasheed
Nov 20 '18 at 4:56
You need a dynamic pivot.
– Zohar Peled
Nov 20 '18 at 4:45
You need a dynamic pivot.
– Zohar Peled
Nov 20 '18 at 4:45
i think, this should be done in presentation layer...
– Abdul Rasheed
Nov 20 '18 at 4:56
i think, this should be done in presentation layer...
– Abdul Rasheed
Nov 20 '18 at 4:56
add a comment |
1 Answer
1
active
oldest
votes
With an unknown depth you will have to go the dynamic SQL route. But such cases tend to have a maximal depth. As your columns will have computable names, you can try this:
I enhanced your table a bit:
CREATE TABLE #tblHRData
(
Emplid INT,
ReportsToEmplid INT,
Descr VARCHAR(100)
)
INSERT INTO #tblHRData (Emplid, ReportsToEmplid, Descr)
VALUES (1, 2, 'lvl 3.2.1') --boss is 2
,(2, 3, 'lvl 3.2') --boss is 3
,(3,null, 'big boss')--big boss reports to no one
,(4, 3, 'lvl 3.4') --one more 2nd lvl
,(5, 4, 'lvl 3.4.5') --below 4
,(6, 4, 'lvl 3.4.6') --another one below 4
-- And I changed the recursive CTE to start off with the big boss and to build a sort string on the fly. In this case this is limited to 3 digits. You'll have to widen this with Emplids exceeding 999:
;WITH CTE AS
(
SELECT
Emplid,
ReportsToEmplid,
Descr,
0 AS EmpLvl,
CAST(REPLACE(STR(Emplid,3),' ','0') AS VARCHAR(MAX)) AS SortOrder
FROM
#tblHRData
WHERE
ReportsToEmplid IS NULL --start with the big boss
UNION ALL
SELECT
child.Emplid,
child.ReportsToEmplid,
child.Descr,
parent.EmpLvl + 1,
parent.SortOrder + REPLACE(STR(child.Emplid,3),' ','0')
FROM
#tblHRData child
JOIN
CTE parent ON child.ReportsToEmplid = parent.Emplid
)
SELECT Emplid,
SortOrder,
MAX(CASE WHEN EmpLvl=0 THEN Descr END) AS BossDescr,
MAX(CASE WHEN EmpLvl=1 THEN Descr END) AS Lvl1Descr,
MAX(CASE WHEN EmpLvl=2 THEN Descr END) AS Lvl2Descr,
MAX(CASE WHEN EmpLvl=3 THEN Descr END) AS Lvl3Descr,
MAX(CASE WHEN EmpLvl=4 THEN Descr END) AS Lvl4Descr,
MAX(CASE WHEN EmpLvl=5 THEN Descr END) AS Lvl5Descr
--add as many as you need and add some more to be future safe
FROM CTE
GROUP BY EmpLvl,Emplid,SortOrder
ORDER BY SortOrder;
GO
DROP TABLE #tblHRData
The result
+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| Emplid | SortOrder | BossDescr | Lvl1Descr | Lvl2Descr | Lvl3Descr | Lvl4Descr | Lvl5Descr |
+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 3 | 003 | big boss | NULL | NULL | NULL | NULL | NULL |
+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 2 | 003002 | NULL | lvl 3.2 | NULL | NULL | NULL | NULL |
+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 1 | 003002001 | NULL | NULL | lvl 3.2.1 | NULL | NULL | NULL |
+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 4 | 003004 | NULL | lvl 3.4 | NULL | NULL | NULL | NULL |
+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 5 | 003004005 | NULL | NULL | lvl 3.4.5 | NULL | NULL | NULL |
+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 6 | 003004006 | NULL | NULL | lvl 3.4.6 | NULL | NULL | NULL |
+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
Some remarks:
- I use the conditional aggregation as PIVOT approach. With just one column this can be done with PIVOT() too.
- The SortOrder is important to be created in the recursion. It is kind of the path to the entry and will allow you to order your result-set.
- This path must allow alphanumerical sorting. Therefore I concatenate padded strings.
answered Nov 20 '18 at 9:56
ShnugoShnugo
48.7k72566
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53385808%2ftsql-manager-hierarchy%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
With an unknown depth you will have to go the dynamic SQL route. But such cases tend to have a maximal depth. As your columns will have computable names, you can try this:
I enhanced your table a bit:
CREATE TABLE #tblHRData
(
Emplid INT,
ReportsToEmplid INT,
Descr VARCHAR(100)
)
INSERT INTO #tblHRData (Emplid, ReportsToEmplid, Descr)
VALUES (1, 2, 'lvl 3.2.1') --boss is 2
,(2, 3, 'lvl 3.2') --boss is 3
,(3,null, 'big boss')--big boss reports to no one
,(4, 3, 'lvl 3.4') --one more 2nd lvl
,(5, 4, 'lvl 3.4.5') --below 4
,(6, 4, 'lvl 3.4.6') --another one below 4
-- And I changed the recursive CTE to start off with the big boss and to build a sort string on the fly. In this case this is limited to 3 digits. You'll have to widen this with Emplids exceeding 999:
;WITH CTE AS
(
SELECT
Emplid,
ReportsToEmplid,
Descr,
0 AS EmpLvl,
CAST(REPLACE(STR(Emplid,3),' ','0') AS VARCHAR(MAX)) AS SortOrder
FROM
#tblHRData
WHERE
ReportsToEmplid IS NULL --start with the big boss
UNION ALL
SELECT
child.Emplid,
child.ReportsToEmplid,
child.Descr,
parent.EmpLvl + 1,
parent.SortOrder + REPLACE(STR(child.Emplid,3),' ','0')
FROM
#tblHRData child
JOIN
CTE parent ON child.ReportsToEmplid = parent.Emplid
)
SELECT Emplid,
SortOrder,
MAX(CASE WHEN EmpLvl=0 THEN Descr END) AS BossDescr,
MAX(CASE WHEN EmpLvl=1 THEN Descr END) AS Lvl1Descr,
MAX(CASE WHEN EmpLvl=2 THEN Descr END) AS Lvl2Descr,
MAX(CASE WHEN EmpLvl=3 THEN Descr END) AS Lvl3Descr,
MAX(CASE WHEN EmpLvl=4 THEN Descr END) AS Lvl4Descr,
MAX(CASE WHEN EmpLvl=5 THEN Descr END) AS Lvl5Descr
--add as many as you need and add some more to be future safe
FROM CTE
GROUP BY EmpLvl,Emplid,SortOrder
ORDER BY SortOrder;
GO
DROP TABLE #tblHRData
The result
+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| Emplid | SortOrder | BossDescr | Lvl1Descr | Lvl2Descr | Lvl3Descr | Lvl4Descr | Lvl5Descr |
+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 3 | 003 | big boss | NULL | NULL | NULL | NULL | NULL |
+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 2 | 003002 | NULL | lvl 3.2 | NULL | NULL | NULL | NULL |
+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 1 | 003002001 | NULL | NULL | lvl 3.2.1 | NULL | NULL | NULL |
+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 4 | 003004 | NULL | lvl 3.4 | NULL | NULL | NULL | NULL |
+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 5 | 003004005 | NULL | NULL | lvl 3.4.5 | NULL | NULL | NULL |
+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 6 | 003004006 | NULL | NULL | lvl 3.4.6 | NULL | NULL | NULL |
+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
Some remarks:
- I use the conditional aggregation as PIVOT approach. With just one column this can be done with PIVOT() too.
- The SortOrder is important to be created in the recursion. It is kind of the path to the entry and will allow you to order your result-set.
- This path must allow alphanumerical sorting. Therefore I concatenate padded strings.
answered Nov 20 '18 at 9:56
ShnugoShnugo
48.7k72566
add a comment |
With an unknown depth you will have to go the dynamic SQL route. But such cases tend to have a maximal depth. As your columns will have computable names, you can try this:
I enhanced your table a bit:
CREATE TABLE #tblHRData
(
Emplid INT,
ReportsToEmplid INT,
Descr VARCHAR(100)
)
INSERT INTO #tblHRData (Emplid, ReportsToEmplid, Descr)
VALUES (1, 2, 'lvl 3.2.1') --boss is 2
,(2, 3, 'lvl 3.2') --boss is 3
,(3,null, 'big boss')--big boss reports to no one
,(4, 3, 'lvl 3.4') --one more 2nd lvl
,(5, 4, 'lvl 3.4.5') --below 4
,(6, 4, 'lvl 3.4.6') --another one below 4
-- And I changed the recursive CTE to start off with the big boss and to build a sort string on the fly. In this case this is limited to 3 digits. You'll have to widen this with Emplids exceeding 999:
;WITH CTE AS
(
SELECT
Emplid,
ReportsToEmplid,
Descr,
0 AS EmpLvl,
CAST(REPLACE(STR(Emplid,3),' ','0') AS VARCHAR(MAX)) AS SortOrder
FROM
#tblHRData
WHERE
ReportsToEmplid IS NULL --start with the big boss
UNION ALL
SELECT
child.Emplid,
child.ReportsToEmplid,
child.Descr,
parent.EmpLvl + 1,
parent.SortOrder + REPLACE(STR(child.Emplid,3),' ','0')
FROM
#tblHRData child
JOIN
CTE parent ON child.ReportsToEmplid = parent.Emplid
)
SELECT Emplid,
SortOrder,
MAX(CASE WHEN EmpLvl=0 THEN Descr END) AS BossDescr,
MAX(CASE WHEN EmpLvl=1 THEN Descr END) AS Lvl1Descr,
MAX(CASE WHEN EmpLvl=2 THEN Descr END) AS Lvl2Descr,
MAX(CASE WHEN EmpLvl=3 THEN Descr END) AS Lvl3Descr,
MAX(CASE WHEN EmpLvl=4 THEN Descr END) AS Lvl4Descr,
MAX(CASE WHEN EmpLvl=5 THEN Descr END) AS Lvl5Descr
--add as many as you need and add some more to be future safe
FROM CTE
GROUP BY EmpLvl,Emplid,SortOrder
ORDER BY SortOrder;
GO
DROP TABLE #tblHRData
The result
+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| Emplid | SortOrder | BossDescr | Lvl1Descr | Lvl2Descr | Lvl3Descr | Lvl4Descr | Lvl5Descr |
+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 3 | 003 | big boss | NULL | NULL | NULL | NULL | NULL |
+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 2 | 003002 | NULL | lvl 3.2 | NULL | NULL | NULL | NULL |
+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 1 | 003002001 | NULL | NULL | lvl 3.2.1 | NULL | NULL | NULL |
+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 4 | 003004 | NULL | lvl 3.4 | NULL | NULL | NULL | NULL |
+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 5 | 003004005 | NULL | NULL | lvl 3.4.5 | NULL | NULL | NULL |
+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 6 | 003004006 | NULL | NULL | lvl 3.4.6 | NULL | NULL | NULL |
+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
Some remarks:
- I use the conditional aggregation as PIVOT approach. With just one column this can be done with PIVOT() too.
- The SortOrder is important to be created in the recursion. It is kind of the path to the entry and will allow you to order your result-set.
- This path must allow alphanumerical sorting. Therefore I concatenate padded strings.
answered Nov 20 '18 at 9:56
ShnugoShnugo
48.7k72566
add a comment |
With an unknown depth you will have to go the dynamic SQL route. But such cases tend to have a maximal depth. As your columns will have computable names, you can try this:
I enhanced your table a bit:
CREATE TABLE #tblHRData
(
Emplid INT,
ReportsToEmplid INT,
Descr VARCHAR(100)
)
INSERT INTO #tblHRData (Emplid, ReportsToEmplid, Descr)
VALUES (1, 2, 'lvl 3.2.1') --boss is 2
,(2, 3, 'lvl 3.2') --boss is 3
,(3,null, 'big boss')--big boss reports to no one
,(4, 3, 'lvl 3.4') --one more 2nd lvl
,(5, 4, 'lvl 3.4.5') --below 4
,(6, 4, 'lvl 3.4.6') --another one below 4
-- And I changed the recursive CTE to start off with the big boss and to build a sort string on the fly. In this case this is limited to 3 digits. You'll have to widen this with Emplids exceeding 999:
;WITH CTE AS
(
SELECT
Emplid,
ReportsToEmplid,
Descr,
0 AS EmpLvl,
CAST(REPLACE(STR(Emplid,3),' ','0') AS VARCHAR(MAX)) AS SortOrder
FROM
#tblHRData
WHERE
ReportsToEmplid IS NULL --start with the big boss
UNION ALL
SELECT
child.Emplid,
child.ReportsToEmplid,
child.Descr,
parent.EmpLvl + 1,
parent.SortOrder + REPLACE(STR(child.Emplid,3),' ','0')
FROM
#tblHRData child
JOIN
CTE parent ON child.ReportsToEmplid = parent.Emplid
)
SELECT Emplid,
SortOrder,
MAX(CASE WHEN EmpLvl=0 THEN Descr END) AS BossDescr,
MAX(CASE WHEN EmpLvl=1 THEN Descr END) AS Lvl1Descr,
MAX(CASE WHEN EmpLvl=2 THEN Descr END) AS Lvl2Descr,
MAX(CASE WHEN EmpLvl=3 THEN Descr END) AS Lvl3Descr,
MAX(CASE WHEN EmpLvl=4 THEN Descr END) AS Lvl4Descr,
MAX(CASE WHEN EmpLvl=5 THEN Descr END) AS Lvl5Descr
--add as many as you need and add some more to be future safe
FROM CTE
GROUP BY EmpLvl,Emplid,SortOrder
ORDER BY SortOrder;
GO
DROP TABLE #tblHRData
The result
+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| Emplid | SortOrder | BossDescr | Lvl1Descr | Lvl2Descr | Lvl3Descr | Lvl4Descr | Lvl5Descr |
+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 3 | 003 | big boss | NULL | NULL | NULL | NULL | NULL |
+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 2 | 003002 | NULL | lvl 3.2 | NULL | NULL | NULL | NULL |
+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 1 | 003002001 | NULL | NULL | lvl 3.2.1 | NULL | NULL | NULL |
+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 4 | 003004 | NULL | lvl 3.4 | NULL | NULL | NULL | NULL |
+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 5 | 003004005 | NULL | NULL | lvl 3.4.5 | NULL | NULL | NULL |
+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 6 | 003004006 | NULL | NULL | lvl 3.4.6 | NULL | NULL | NULL |
+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
Some remarks:
- I use the conditional aggregation as PIVOT approach. With just one column this can be done with PIVOT() too.
- The SortOrder is important to be created in the recursion. It is kind of the path to the entry and will allow you to order your result-set.
- This path must allow alphanumerical sorting. Therefore I concatenate padded strings.
answered Nov 20 '18 at 9:56
ShnugoShnugo
48.7k72566
With an unknown depth you will have to go the dynamic SQL route. But such cases tend to have a maximal depth. As your columns will have computable names, you can try this:
I enhanced your table a bit:
CREATE TABLE #tblHRData
(
Emplid INT,
ReportsToEmplid INT,
Descr VARCHAR(100)
)
INSERT INTO #tblHRData (Emplid, ReportsToEmplid, Descr)
VALUES (1, 2, 'lvl 3.2.1') --boss is 2
,(2, 3, 'lvl 3.2') --boss is 3
,(3,null, 'big boss')--big boss reports to no one
,(4, 3, 'lvl 3.4') --one more 2nd lvl
,(5, 4, 'lvl 3.4.5') --below 4
,(6, 4, 'lvl 3.4.6') --another one below 4
-- And I changed the recursive CTE to start off with the big boss and to build a sort string on the fly. In this case this is limited to 3 digits. You'll have to widen this with Emplids exceeding 999:
;WITH CTE AS
(
SELECT
Emplid,
ReportsToEmplid,
Descr,
0 AS EmpLvl,
CAST(REPLACE(STR(Emplid,3),' ','0') AS VARCHAR(MAX)) AS SortOrder
FROM
#tblHRData
WHERE
ReportsToEmplid IS NULL --start with the big boss
UNION ALL
SELECT
child.Emplid,
child.ReportsToEmplid,
child.Descr,
parent.EmpLvl + 1,
parent.SortOrder + REPLACE(STR(child.Emplid,3),' ','0')
FROM
#tblHRData child
JOIN
CTE parent ON child.ReportsToEmplid = parent.Emplid
)
SELECT Emplid,
SortOrder,
MAX(CASE WHEN EmpLvl=0 THEN Descr END) AS BossDescr,
MAX(CASE WHEN EmpLvl=1 THEN Descr END) AS Lvl1Descr,
MAX(CASE WHEN EmpLvl=2 THEN Descr END) AS Lvl2Descr,
MAX(CASE WHEN EmpLvl=3 THEN Descr END) AS Lvl3Descr,
MAX(CASE WHEN EmpLvl=4 THEN Descr END) AS Lvl4Descr,
MAX(CASE WHEN EmpLvl=5 THEN Descr END) AS Lvl5Descr
--add as many as you need and add some more to be future safe
FROM CTE
GROUP BY EmpLvl,Emplid,SortOrder
ORDER BY SortOrder;
GO
DROP TABLE #tblHRData
The result
+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| Emplid | SortOrder | BossDescr | Lvl1Descr | Lvl2Descr | Lvl3Descr | Lvl4Descr | Lvl5Descr |
+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 3 | 003 | big boss | NULL | NULL | NULL | NULL | NULL |
+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 2 | 003002 | NULL | lvl 3.2 | NULL | NULL | NULL | NULL |
+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 1 | 003002001 | NULL | NULL | lvl 3.2.1 | NULL | NULL | NULL |
+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 4 | 003004 | NULL | lvl 3.4 | NULL | NULL | NULL | NULL |
+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 5 | 003004005 | NULL | NULL | lvl 3.4.5 | NULL | NULL | NULL |
+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 6 | 003004006 | NULL | NULL | lvl 3.4.6 | NULL | NULL | NULL |
+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
Some remarks:
- I use the conditional aggregation as PIVOT approach. With just one column this can be done with PIVOT() too.
- The SortOrder is important to be created in the recursion. It is kind of the path to the entry and will allow you to order your result-set.
- This path must allow alphanumerical sorting. Therefore I concatenate padded strings.
answered Nov 20 '18 at 9:56
ShnugoShnugo
48.7k72566
answered Nov 20 '18 at 9:56
ShnugoShnugo
48.7k72566
answered Nov 20 '18 at 9:56
ShnugoShnugo
48.7k72566
answered Nov 20 '18 at 9:56
ShnugoShnugo
48.7k72566
48.7k72566
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53385808%2ftsql-manager-hierarchy%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
You need a dynamic pivot.
– Zohar Peled
Nov 20 '18 at 4:45
i think, this should be done in presentation layer...
– Abdul Rasheed
Nov 20 '18 at 4:56