Merge two columns into one and add one extra column

Question

I want to merge two columns into one column and want to add one extra column to show relationship between two columns as shown below in the example.

Example

Here is the employee table example:

Table: Employee

create table employee
(
 name varchar(10),
 empid1 varchar(10),
 empid2 varchar(10)
 );

Inserting some records:

 insert into employee values('ax','A101','X101');
 insert into employee values('by','B101','Y101');
 insert into employee values('cz','C101','Z101');
 insert into employee values('dw','D101','W101');

select * from employee;

name   empid1  empid2
---------------------
 abc    A101    X101
 by     B101    Y101
 cz     C101    Z101
 dw     D101    W101

Note: Now I want to merge empid1 and empid2 into one and also want to add one extra column to show relationship between two columns as shown below in the expected result.

Expected Result:

name   IdType  IdValues
-----------------------    
abc     empid1   A101
by      empid1   B101
cz      empid1   C101
dw      empid1   D101
abc     empid2   X101
by      empid2   Y101
cz      empid2   Z101
dw      empid2   W101

GarethD · Accepted Answer · 2014-10-03 06:53:17Z

You can use UNPIVOT to achieve this:

SELECT  upvt.Name,
        upvt.IDType,
        upvt.IDValues
FROM    Employee AS e
        UNPIVOT
        (   IDValues
            FOR IDType IN ([empid1], [empid2])
        ) AS upvt
ORDER BY upvt.IDType;

Example on SQL Fiddle

An alternative is to use CROSS APPLY with a Table Value Constructor:

SELECT  e.Name,
        upvt.IDType,
        upvt.IDValues
FROM    Employee AS e
        CROSS APPLY
        (VALUES
            ('empid1', e.empid1),
            ('empid2', e.empid2)
        ) AS upvt (IDType, IDValues)
ORDER BY upvt.IDType;

Example on SQL Fiddle

UNPIVOT works fine for this scenario, but the CROSS APPLY approach is more versatile if you need to unpivot more columns, or do something slightly more complex.

Collectives™ on Stack Overflow

Merge two columns into one and add one extra column

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related