Click here to Skip to main content
14,669,092 members
Home » Discussions » Database
   

Database

 
QuestionNeed a demo or tutorial of a simple SQL Database with a 3 Layer modell Pin
Schudi12-Dec-17 3:27
MemberSchudi12-Dec-17 3:27 
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 
I am trying to improve my CTE knowledge and am stuck on a point that I am hoping that someone can help me with. I have not found this in the forum, so if you know of a post that covers this, please point me towards it.

Using the sample off the MS CTE page:

<pre lang="SQL">CREATE TABLE dbo.MyEmployees
(
EmployeeID smallint NOT NULL,
FirstName nvarchar(30) NOT NULL,
LastName nvarchar(40) NOT NULL,
Title nvarchar(50) NOT NULL,
DeptID smallint NOT NULL,
ManagerID int NULL,
CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC)
);
-- Populate the table with values.
INSERT INTO dbo.MyEmployees VALUES
(1, N'Ken', N'Sánchez', N'Chief Executive Officer',16,NULL)
,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1)
,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273)
,(275, N'Michael', N'Blythe', N'Sales Representative',3,274)
,(276, N'Linda', N'Mitchell', N'Sales Representative',3,274)
,(285, N'Syed', N'Abbas', N'Pacific Sales Manager',3,273)
,(286, N'Lynn', N'Tsoflias', N'Sales Representative',3,285)
,(16, N'David',N'Bradley', N'Marketing Manager', 4, 273)
,(23, N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);

WITH DirectReports (ManagerID, EmployeeID, Title, Level)
AS
(
-- Anchor member definition
SELECT e.ManagerID, e.EmployeeID, e.Title,
0 AS Level
FROM dbo.MyEmployees AS e
WHERE ManagerID IS NULL
UNION ALL
-- Recursive member definition
SELECT e.ManagerID, e.EmployeeID, e.Title,
Level + 1
FROM dbo.MyEmployees AS e
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID
)
-- Statement that executes the CTE
SELECT ManagerID, EmployeeID, Title, Level
FROM DirectReports
order by level, managerid
GO</pre>

Gives this, which I am pretty much understanding.

ManagerID EmployeeID Title Level
NULL 1 Chief Executive Officer 0
1 273 Vice President of Sales 1
273 16 Marketing Manager 2
273 274 North American Sales Manager 2
273 285 Pacific Sales Manager 2
16 23 Marketing Specialist 3
274 275 Sales Representative 3
274 276 Sales Representative 3
285 286 Sales Representative 3

What I would like to figure out is how to modify the query to group the data differently, in this example show a manager, then anyone who reports to them, then the next manager. It seems like a simple concept, but I will be damned if I have been able to figure it out.



ManagerID EmployeeID Title Level
NULL 1 Chief Executive Officer 0
1 273 Vice President of Sales 1
273 16 Marketing Manager 2
16 23 Marketing Specialist 3
273 274 North American Sales Manager 2
274 275 Sales Representative 3
274 276 Sales Representative 3
273 285 Pacific Sales Manager 2
285 286 Sales Representative 3

</pre>

Thanks for reading.
AnswerRe: CTE Sub-Sort Pin
Richard Deeming12-Dec-17 1:56
mveRichard Deeming12-Dec-17 1:56 
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 

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.