SELECT
e.EmpId
,e.EmpNo
,FirstName
,LastName
,Email
d.DepartmantName
t.TeamName
FROM e.Employee e
JOIN EmployeeTeam et
ON e.EmpId = et.EmpId
JOIN Team t
ON eg.TeamId = t.TeamId
JOIN TeamArea ta
ON ta.TeamId = t.TeamId
JOIN Department d
ON d.DepartmentId = ta.DepartmentId
Where EmpId is primary key in Employee table and EmpNo is not unique and key field.
I need to add a new column named Id and I need to this column be unique so I think to add new column as identity.
If that is possible how to do this?
The query result is fine. Except I don't have have unique identifier field in the result.
I tried to add ID like:
SELECT ID = ISNULL(cast(newid() as varchar(40)), ''),
and then rest the code of query.
But in that case I'm getting duplication for each row.
It needs me for Entity Framework.
You can generate an unique number on the fly by using ROW_NUMBER().
If the order is unique then the result will be deterministic assuming the data does not change.
SELECT
e.EmpId
,e.EmpNo
,FirstName
,LastName
,Email
,d.DepartmantName
,t.TeamName
,ROW_NUMBER() OVER (ORDER BY e.EmpId, t.TeamId, ta.DepartmentId) AS rn
FROM e.Employee e
JOIN EmployeeTeam et
ON e.EmpId = et.EmpId
JOIN Team t
ON eg.TeamId = t.TeamId
JOIN TeamArea ta
ON ta.TeamId = t.TeamId
JOIN Department d
ON d.DepartmentId = ta.DepartmentId;
Hi Ifor. Thanks for reply.
Yesterday I tried ROW_NUMBER() also, but it destroyed query result.If you have idea how to add ID and keep correct result I will appreciate for help.
Thanks.
Not sure what you mean by "destroyed query result". The ROW_NUMBER() should just add a sequential number to the output, and it's the only way to do that thru within a view.
Hi ScottPletcher. Thanks for reply.
I expected the same like you said. But in actually I got in the result of query some record repeats up to 10 times, some less, some didn't duplicated. I got very bad result of the query when ROW_NUMBER() was added.
Using row_number() will not add rows to the result and 'create' duplicates. This leads me to believe the underlying query already has duplicate values and the row_number() exposed those duplicates.
If an Employee is in many teams - how do you want that information to be identified?
If you just want to assign an arbitrary ID value - then use: row_number() over(order by @@spid)
That will generate an ID value across the full set of data returned from the query. But - it will be arbitrary and it can (probably will) be different for every execution. Which brings us back to the original question - why do you need to do this and what problem are you trying to resolve?
You are right. In the result of query the fields such as EmpId, EmpNo, FirstName, LastName, EmailAddress are duplicating as many time as many TeamName exists for according employee.