2

I have a table shown below:

  EmpId     IdType     Id_Number
-------   --------   -----------
 112211     MRN         30013
 112211    Member_Id    40012
 223344     MRN         23432

And i want cell values MRN, Member_ID appear as column names in the resulting table as shown below:

 EmpId     MRN       Member_Id
 -------   --------   -----------
 112211    30013       40012
 223344    23432       Null

I guess i need to use PIVOT. But it requires to use aggregate functions. Since there is no aggregate function to use here, I am not finding any other to accomplish this.

Any help is much appreciated.

BenMorel
  • 34,448
  • 50
  • 182
  • 322
Maltesh
  • 383
  • 2
  • 6
  • 14

1 Answers1

1

This is a straightforward pivot - remember that MIN() or MAX() can be used as aggregates, even on non numeric columns.

SELECT EmpID, MRN, Member_ID
FROM
  Employee
PIVOT 
(
  MIN(ID_Number) FOR
  IDType IN ([MRN], [Member_ID])
)  AS p;

SqlFiddle here

Also, note that it is possible to do this without Pivot- see here

Community
  • 1
  • 1
StuartLC
  • 104,537
  • 17
  • 209
  • 285