Create Non duplicate view in SQL

Question

I have two tables

Developers
- Developer_Id
- Full Name
- Cellphone number
- Email address
- Team
- Role
- Assigned To
JobList
- Job_Id
- Assignment Name
- Assignment Description
- Created By
- Date Created
- Due Date
- Status

I want to view the JobList table but it should include:

Developers.Full Name
Developers.Team
Developers.Role
Developers.Assigned To

I created a view but it return duplicate values like this:

is there any way I can view the JobList table in a single row but include the muliple rows from the Developers table, something like this

Here is the code I use

Create Tables:

CREATE TABLE [dbo].[Developers] (
[Developer_Id]     INT          NOT NULL,
[Full Name]        VARCHAR (50) NULL,
[Cellphone number] VARCHAR (50) NULL,
[Email address]    VARCHAR (50) NULL,
[Team]             VARCHAR (50) NULL,
[Role]             VARCHAR (50) NULL, 
[Assigned To] INT NULL, 
CONSTRAINT [PK_Developers] PRIMARY KEY ([Developer_Id])
);

CREATE TABLE [dbo].[JobList] (
[Job_Id]                 INT           NOT NULL,
[Assignment Name]        VARCHAR (50)  NULL,
[Assignment Description] VARCHAR (MAX) NULL,
[Created By]             VARCHAR (50)  NULL,
[Date Created]           DATE          NULL,
[Due Date]               DATE          NULL, 
[Status] VARCHAR(50) NULL, 
CONSTRAINT [PK_JobList] PRIMARY KEY ([Job_Id])
);

Create view

CREATE VIEW [dbo].AssignedJobs
AS SELECT JobList.[Assignment Name], JobList.[Assignment Description], JobList.[Due Date], JobList.[Status],Developers.[Full Name], Developers.[Role]
from JobList
inner join Developers
on JobList.Job_Id = Developers.[Assigned To]

The result you get is how joins work, you cannot have a single row that contains multiple rows. — Simon, Jun 08 '17 at 09:47
Your expected output does not correspond to anything SQL Server would actually output. Could you live with the full names appearing as CSV, e.g. `User1, User2`? Or do you want something else? — Tim Biegeleisen, Jun 08 '17 at 09:48
You could also pivot the developer names to columns. https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server — Simon, Jun 08 '17 at 09:49

score 0 · Answer 1 · answered Jun 08 '17 at 09:58

CREATE VIEW [dbo].AssignedJobs
AS SELECT JobList.[Assignment Name], JobList.[Assignment Description], 
JobList.[Due Date], JobList.[Status],Developers.[Full Name], Developers.[Role]
from JobList
inner join Developers
on JobList.Job_Id = Developers.[Assigned To]
group by JobList.[Assigment Name], JobList.[Assignment Description], JobList.[Due Date], JobList[Status], string_agg(Developers.[Full Name], ' ') as [Full Name], string_agg (Developers.Role, ' ') as Role

If you are not on SQL Server 2017/Azure then use something like this: Agg with Stuff and XML FOR PATH

Create Non duplicate view in SQL

1 Answers1