Click here to Skip to main content
14,663,735 members
Home » Discussions » Database
   

Database

 
AnswerRe: Need a demo or tutorial of a simple SQL Database with a 3 Layer modell Pin
Richard MacCutchan12-Dec-17 5:35
mveRichard MacCutchan12-Dec-17 5:35 
AnswerRe: Need a demo or tutorial of a simple SQL Database with a 3 Layer modell Pin
Nathan Minier12-Dec-17 5:50
professionalNathan Minier12-Dec-17 5:50 
GeneralRe: Need a demo or tutorial of a simple SQL Database with a 3 Layer modell Pin
Schudi12-Dec-17 6:22
MemberSchudi12-Dec-17 6:22 
GeneralRe: Need a demo or tutorial of a simple SQL Database with a 3 Layer modell Pin
Nathan Minier12-Dec-17 7:10
professionalNathan Minier12-Dec-17 7:10 
GeneralRe: Need a demo or tutorial of a simple SQL Database with a 3 Layer modell Pin
Richard MacCutchan12-Dec-17 8:01
mveRichard MacCutchan12-Dec-17 8:01 
GeneralRe: Need a demo or tutorial of a simple SQL Database with a 3 Layer modell Pin
Schudi12-Dec-17 8:13
MemberSchudi12-Dec-17 8:13 
QuestionCTE Sub-Sort Pin
Stan Lake11-Dec-17 11:52
professionalStan Lake11-Dec-17 11:52 
AnswerRe: CTE Sub-Sort Pin
Richard Deeming12-Dec-17 1:56
mveRichard Deeming12-Dec-17 1:56 
Something like this should work:
WITH DirectReports As
(
    SELECT 
        e.ManagerID, 
        e.EmployeeID, 
        e.Title,
        0 AS Level,
        CAST(e.EmployeeID As varchar(max)) As Path
    FROM 
        @T As e
    WHERE 
        ManagerID IS NULL

    UNION ALL

    SELECT 
        e.ManagerID, 
        e.EmployeeID, 
        e.Title,
        Level + 1,
        Path + '/' + CAST(e.EmployeeID As varchar(max))
    FROM 
        @T As e
        INNER JOIN DirectReports As d
        ON e.ManagerID = d.EmployeeID
)
SELECT 
    ManagerID, 
    EmployeeID, 
    Title, 
    Level,
    Path
FROM 
    DirectReports
ORDER BY 
    Path
;
Output:
ManagerID    EmployeeID    Title                           Level    Path
NULL         1             Chief Executive Officer         0        1
1            273           Vice President of Sales         1        1/273
273          16            Marketing Manager               2        1/273/16
16           23            Marketing Specialist            3        1/273/16/23
273          274           North American Sales Manager    2        1/273/274
274          275           Sales Representative            3        1/273/274/275
274          276           Sales Representative            3        1/273/274/276
273          285           Pacific Sales Manager           2        1/273/285
285          286           Sales Representative            3        1/273/285/286

Alternatively, you could use the hierarchyid[^] type:
Tutorial: Using the hierarchyid Data Type | Microsoft Docs[^]



"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer


GeneralRe: CTE Sub-Sort Pin
Stan Lake12-Dec-17 2:35
professionalStan Lake12-Dec-17 2:35 
GeneralRe: CTE Sub-Sort Pin
Mycroft Holmes12-Dec-17 12:45
professionalMycroft Holmes12-Dec-17 12:45 
QuestionTable Design Suggestion Pin
Manish K. Agarwal7-Dec-17 19:42
MemberManish K. Agarwal7-Dec-17 19:42 
AnswerRe: Table Design Suggestion Pin
Nathan Minier8-Dec-17 1:33
professionalNathan Minier8-Dec-17 1:33 
GeneralRe: Table Design Suggestion Pin
Manish K. Agarwal10-Dec-17 17:46
MemberManish K. Agarwal10-Dec-17 17:46 
GeneralRe: Table Design Suggestion Pin
Nathan Minier11-Dec-17 1:38
professionalNathan Minier11-Dec-17 1:38 
AnswerRe: Table Design Suggestion Pin
Richard Deeming8-Dec-17 1:35
mveRichard Deeming8-Dec-17 1:35 
GeneralRe: Table Design Suggestion Pin
Manish K. Agarwal10-Dec-17 17:53
MemberManish K. Agarwal10-Dec-17 17:53 
AnswerRe: Table Design Suggestion Pin
Mycroft Holmes9-Dec-17 0:00
professionalMycroft Holmes9-Dec-17 0:00 
QuestionCan the Pivot operator be used with text columns? Pin
Richard Andrew x646-Dec-17 4:14
professionalRichard Andrew x646-Dec-17 4:14 
AnswerRe: Can the Pivot operator be used with text columns? Pin
Richard Deeming6-Dec-17 9:51
mveRichard Deeming6-Dec-17 9:51 
GeneralRe: Can the Pivot operator be used with text columns? Pin
Richard Andrew x646-Dec-17 12:16
professionalRichard Andrew x646-Dec-17 12:16 
AnswerRe: Can the Pivot operator be used with text columns? Pin
Jörgen Andersson6-Dec-17 10:05
professionalJörgen Andersson6-Dec-17 10:05 
GeneralRe: Can the Pivot operator be used with text columns? Pin
Richard Andrew x646-Dec-17 12:17
professionalRichard Andrew x646-Dec-17 12:17 
QuestionSQL and database tutorials Pin
MikeTheFid4-Dec-17 11:52
MemberMikeTheFid4-Dec-17 11:52 
AnswerRe: SQL and database tutorials Pin
Mycroft Holmes4-Dec-17 13:17
professionalMycroft Holmes4-Dec-17 13:17 
GeneralRe: SQL and database tutorials Pin
MikeTheFid5-Dec-17 0:33
MemberMikeTheFid5-Dec-17 0:33 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.