Okay, I am very new to SQL and I apologize in advance for incorrect etiquette or seemingly silly mistakes :unsure:. Here is what I have according to the link:
For the Employee Table:
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..dbo.Employee','U') IS NOT NULL
DROP TABLE dbo.Employee
--===== Create the test table with
CREATE TABLE dbo.Employee
(
Employee_ID INT, PRIMARY KEY, ---==This is the Primary Key in the dbo.Employee Table
CC FLOAT,
First NVARCHAR(50)
Last NVARCHAR(50),
Space_Number INT
[Employee Type] NVARCHAR(50)
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT dbo.Employee ON
--===== Insert the test data into the test table
INSERT INTO dbo.Employee
(Employee_ID, CC, First, Last, Space_Number, [Empoyee Type])
SELECT '20','9700','Joseph','McSmith','457''(F.T)' UNION ALL
SELECT '21','4800','Pat','Dunlovey','1456''(F.T)' UNION ALL
SELECT '24','9150','Scott','Chatting','638''(F.T)' UNION ALL
SELECT '25','9150','Brian','Hutchin','1270''(F.T)' UNION ALL
SELECT '779','9150','Peter','Galant','1270''(F.T)' UNION ALL
SELECT '26','8400','Bob','Pryt','629''(F.T)' UNION ALL
SELECT '27','7570','Karen','Home','620''(F.T)' UNION ALL
SELECT '28','7400','Helen','Mill','609''(F.T)' UNION ALL
SELECT '30','7400','Kimberly','Parth','608''(F.T)'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT dbo.Employee OFF
For the Spaces Table:
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..dbo.Spaces','U') IS NOT NULL
DROP TABLE dbo.Spaces
--===== Create the test table with
CREATE TABLE dbo.Spaces
(
Space_ID INT, PRIMARY KEY, ---==This is the Primary Key in the dbo.Spaces Table
[Building #] NVARCHAR(50),
Floor NVARCHAR(50)
Space_Number NVARCHAR(255),
[Space Type] NVARCHAR(50)
Area INT
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT dbo.Spaces ON
--===== Insert the test data into the test table
INSERT INTO dbo.Employee
(Space_ID, [Building #], Floor, Space_Number, [Space Type], Area)
SELECT '457','84','4S','84R4141','Office','194' UNION ALL
SELECT '608','84','2S','84R2121','Office','130' UNION ALL
SELECT '609','84','2S','84R2120','Office','126' UNION ALL
SELECT '620','84','1N','84R1576','Storage','1002' UNION ALL
SELECT '629','84','1N','84R1558','Office','109' UNION ALL
SELECT '638','84','1N','84R1544','Workspace','69' UNION ALL
SELECT '1270','158','1','158R1533','Office','139' UNION ALL
SELECT '1456','158','3','158R3111','Office','208'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT dbo.Spaces OFF
What I need to do is, for each Space_Number, have a unique row in the view. For example, above we have Brian Hutchin and Peter Galant sharing a Space_Number (Note, this is equivalent to the Space_ID in the dbo.Spaces table). The view I have now shows two separate rows for these two employees. I want to have their names displayed next to eachoter in the same row with the same space_number (space_id).
The necessary columns would then, in this new view, be: Space_ID, Space_Number, First1, Last1, First2, Last2, FirstofCC, FirstofEmployeeType, Building #, Key. Here, First1/Last1 would be the name of the first employee in the space_number, likewise for the second employee would be First2/Last2. The rest of the data would pertain to the first employee. It is not necessary to have a dynamically changing # of columns. If there is no second employee, I would simply like a null value to be shown.
Hope that this sheds more light on my problem. Please let me know any other way I can help you to allow you to help me. Thank you for your time, it is much appreciated.