|
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.
|
|
|
|
|
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
|
|
|
|
|
Thank you. That was driving me nuts. I was very close, but kept just missing. Plus, thanks for the tip on hierarchyid. I will be digging into that, as soon as I get this working inside my design.
|
|
|
|
|
Psst the best way to thank Richard (the CTE guru) is to up vote his response. Voting arrows appear on the left of the message.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have to maintain some metadata lists in DB, I have 2 options to design underlying simple table,
1st:
NAME VALUE
-------------------
dept HR
dept fin
role engineer
role designer
UNIQUE CONSTRAINT (NAME, VALUE) and some other columns like auto generated ID, etc.
2nd:
NAME VALUE_JSON_CLOB
-------------------
dept {["HR", "fin"]}
role {["engineer", "designer"}]
UNIQUE CONSTRAINT (NAME) and some other columns like auto generated ID, etc.
There is no DELETE operation, only SELECT and INSERT/UPDATE. In first advantage is only INSERT is required but SELECT (fetch all values for a given NAME) will be slow. In second SELECT will be fast but UPDATE will be slow. By considering there could be 1000s of such lists with 1000s for possible values in the system with frequent SELECTs and less INSERTs, which TABLE design will be good in terms of performance.
Thanks in advance.
|
|
|
|
|
Performance is a meaningless metric unless we know what your use case is.
Also, the second is more like an object store, and would do better in a non-relational (NoSQL) system.
"There are three kinds of lies: lies, damned lies and statistics."
- Benjamin Disraeli
|
|
|
|
|
As described above as well:
There is no DELETE operation, only SELECT and INSERT/UPDATE. Also every time I want to select all possible values for given NAME.
This is a kind of reference list to fill the autocomplete suggestions on UI. Hence fetch will be always all values and INSERT new only one value at a time.
|
|
|
|
|
Manish K. Agarwal wrote: Also every time I want to select all possible values for given NAME.
Not exactly what you said above, to be fair. It also doesn't say whether this is native or web-based, which has a relevant impact on performance of the system as a whole.
If writes are rare in either case, you'll see better SELECT performance out of option 2 for one simple reason: assuming that you index the key, when it's found the appropriate key, it's done and will stop searching the table. If you're in a web environment, you have the added benefit of being able to send it straight down the pipe with no serialization/deserialization required. If all your model requires is selection speed, option 2 is the way to go.
Option 2 is definitely not write friendly, though, nor can you use it to relate data down the road on the database level. If these are likely or even possible considerations, you might want to go with option 1.
"There are three kinds of lies: lies, damned lies and statistics."
- Benjamin Disraeli
|
|
|
|
|
I'd immediately rule out the second option. You should always try to avoid storing multiple values in a single column.
Assuming the data is meant to be a set of look-ups for other records, I'd also try avoid the first option. Put each list in its own table - Departments, Roles, etc. That way, you'll be able to define foreign key relationships from the tables that reference them.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
It is a complex system where I don't know upfront what all possible reference list may exist in the runtime environment. Also, these value will never be referenced from any other table or column. A kind of auto-suggest list and user is always free to choose new value if not like any of the suggestions.
|
|
|
|
|
Listen to Richard, I recently got told that us old folks did not know the modern way of doing things the "architect" want to put all the lookup data into 1 table. He ended up with 5 table and a dogs breakfast of different relationships maintained in the business layer and not the database where I wanted it.
Use 1 table for each different type of lookup data, use a primary key (not a code) and foreign keys.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I am new to the Pivot operator in SQL Server.
I see that the Pivot operator requires an aggregate function in order to work.
However, I need to pivot a text column, and I can't find any examples that show how to do it.
Am I barking up the wrong tree, or is there a way to pivot a text column?
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
|
Thank you, Richard.
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
As long as you never have more than one value per row and column it doesn't matter very much which function you use as long as it works with text. (Min, Max or user defined).
But depending on your data it might be possible to get more than one value per cell, then you need to choose what to show. That's what the aggregate function is for.
|
|
|
|
|
Thank you Jörgen.
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
This is a call for resources.
I have been to 2 interviews recently and totally blew the technical written tests that involved SQL and SQL Server.
Though I've used SQL and done database design in the past, my current role hasn't afforded me any substantial experience in the last 5 or 6 years.
Can you recommend any books or online courses on SQL, T-SQL, and SQL Server programming to get me back up to speed?
I was on Amazon and found a bunch, but I figure you people would know better. 
Cheers,
Mike Fidler
"I intend to live forever - so far, so good." Steven Wright
"I almost had a psychic girlfriend but she left me before we met." Also Steven Wright
"I'm addicted to placebos. I could quit, but it wouldn't matter." Steven Wright yet again.
|
|
|
|
|
|
Thanks. Looks great. I signed up.
Cheers,
Mike Fidler
"I intend to live forever - so far, so good." Steven Wright
"I almost had a psychic girlfriend but she left me before we met." Also Steven Wright
"I'm addicted to placebos. I could quit, but it wouldn't matter." Steven Wright yet again.
|
|
|
|
|
Hi
How to get information from one database.
like only required columns by select * from sys.sysdatabase
|
|
|
|
|
|
Your question is not very clear.
Are you after certain columns from the sys.sysdatabase view?
Are you after looking at certain information about a database?
Are you looking at comparing tables within one or more databases?
Every day, thousands of innocent plants are killed by vegetarians.
Help end the violence EAT BACON
|
|
|
|
|
Hello All,
I am trying to schedule to run a trace file, around 12:30am to 1:00am. Our client is sending us some data, that we need to analyze.
With that said, i can see we can set an "Enable Trace stop time", on the file, but not able to find an option to set the begin time?
Is there a way we can do this in the SQL Profiler?
Thanks!
|
|
|
|
|
|
VK19 wrote: Our client is sending us some data, that we need to analyze.
Why would you need to schedule this? Is the 'client' putting the data directly into the database?
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
