How to merge three columns into one "new" column,

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to merge three columns into one "new" column,

Author

Topic

marksquall
Starting Member

17 Posts

Posted - 2012-04-26 : 01:28:30

Hello Sirs/Mams,

A pleasant day to every one.

I have the following tables and a sample data:
--------------------------------------------------------
PERSON table:


person_id      first_name         last_name
1              PETER              PARKER
2              BRUCE              BANNER
3              TONY               STARK

--------------------------------------------------------
STUDENT table:


person_id      student_number
1              STUD-001

--------------------------------------------------------
FACULTY table:


person_id      faculty_number
2              FACT-001

--------------------------------------------------------
EMPLOYEE table:


person_id      employee_number
3              EMPL-001

--------------------------------------------------------

The person_id in PERSON table is a primary key and the three person_id in STUDENT, FACULTY, EMPLOYEE are foreign keys respectively.

How can I combine the student_number, faculty_number and employee_number columns into ONE column and give it a new name, say ID? This is the expected output:


id          first_name         last_name
STUD-001       PETER              PARKER
FACT-001       BRUCE              BANNER
EMPL-001       TONY               STARK

I tried a lot of combinations but it seems I can not get it right.

I hope some one could help me construct a SQL statement/stored procedure on how to achieve this.

Thank you and more power!

Warm regards,

Mark Squall

caravanpunk
Starting Member

20 Posts

Posted - 2012-04-26 : 01:59:27

Declare @table table(person_ID int,first_name varchar(20),last_name varchar(20))
insert into @table VALUES (1,'PETER','PARKER')
INSERT INTO @table VALUES (2,'BRUCE','BANNER')
INSERT INTO @table VALUES (3,'TONY','STARK')

DECLARE @STUDENT_TABLE TABLE (PERSON_id INT,STUDENT_NUMBER VARCHAR(20))
INSERT INTO @STUDENT_TABLE VALUES (1,'STUD-001')

DECLARE @FACULTY TABLE (PERSON_id INT,FACULTY_NUMBER VARCHAR(20))
INSERT INTO @FACULTY VALUES (2,'FACT-001')

DECLARE @EMPLOYEE TABLE (PERSON_id INT,EMPLOYEE_NUMBER VARCHAR(20))
INSERT INTO @EMPLOYEE VALUES (3,'EMPL-001')

SELECT A.STUDENT_NUMBER AS ID, B.FIRST_NAME,B.last_name FROM @table B
INNER JOIN @STUDENT_TABLE A ON A.PERSON_ID=B.person_ID
UNION ALL
SELECT A.FACULTY_NUMBER AS ID, B.FIRST_NAME,B.last_name FROM @table B
INNER JOIN @FACULTY A ON A.PERSON_ID=B.person_ID
UNION ALL
SELECT A.EMPLOYEE_NUMBER AS ID, B.FIRST_NAME,B.last_name FROM @table B
INNER JOIN @EMPLOYEE A ON A.PERSON_ID=B.person_ID
ORDER BY ID DESC