Convert row values as columns in SQL Server

Question

Table:

CompanyID   Lead   LeadManager
------------------------------
    1          2         3

Required output:

CompanyID   Role          RoleID
--------------------------------
   1        Lead            2
   1        Leadmanager     3

Answer 1

You can use union all to unpivot your dataset. This is a standard solution that works across most (if not all) RDBMS:

select companyID, 'Lead' role, Lead from mytable
union all select companyID, 'LeadManager', LeadManager from mytable

Answer 2

You can use apply to unpivot the data:

select v.*
from t cross apply
     (values (t.CompanyId, 'Lead', t.Lead),
             (t.CompanyId, 'LeadManager', t.LeadManager)
     ) v(CompanyId, Role, RoleId);

The advantage to this approach is that it scans the original table only once. This can be particular helpful when the "table" is a complex query.