Convert SQL Row values into Columns

Question

I have an Employee table which gives below result when I do the query as in SQL :

 Select ID, Role as "Role Name", Employee as "Employee Name"
from employee

Now, I want to convert above query result as per below image. I used Pivot with max and min & then did union, but it's giving only two employee name corresponding to the Role name. I need to get the result as below. Any suggestion, please?

What have you tried so far? I suggest looking up `CASE`. Also, when posting data, please don't use images, they are often less than helpful to other users, instead post your data as text. Thanks. — Thom A, Jun 16 '18 at 14:42
Could the number of roles change? I.e. could you end up having more (or less, I suppose). — Thom A, Jun 16 '18 at 15:00

jophab · Accepted Answer · 2018-06-16T15:38:38.707

select
       id,

       case when [Role Name] = 'Consultant' 
            then [Employee Name] else null 
       end as Consultant,

       case when [Role Name] = 'Manager' 
            then [Employee Name] else null 
       end as Manager
from
     employee

You can add more case statements if you have other roles

Using Pivot,

select 
    id,
    Consultant, 
    Manager
from 
    ( select 
        id,
        [Role Name],
        [Employee Name], 
        row_number() over( partition by id order by id) as rn 
      from 
        employee
    ) src 
pivot 
    ( max([Employee Name]) for [Role Name] in (Consultant,Manager) ) as pvt ;

Update

If you are having many roles or non fixed number of roles, this may not be a good solution.

One way to this is dynamic pivot

The `ELSE NULL` isn't specifically needed here. If a `CASE` expression doesn't evaluate, it'll return `NULL` anyway. :) — Thom A, Jun 16 '18 at 14:47

score 1 · Answer 2 · edited Jul 23 '18 at 06:45

1

`Jophab's approach is a very good approach. If you are learning SQL, here is another method to consider:

select id, employeename as consultant, null as manager
from employee
where rolename = 'Consultant'
union all
select id, null as employeename as manager
from employee
where rolename = 'Manager';

This requires scanning the employee table twice -- which is pretty trivial for small tables.

EDIT:

Another method that doesn't require a bunch of case expressions uses join:

select e.id, v.*
from employee e join
     (values ('consultant', employeename, null),
             ('manager',  null, employeename)
     ) v(rolename, consultant, manager)
     on e.rolename = v.rolename;

You do have to type in a bunch of nulls, though, for each row in values().

edited Jul 23 '18 at 06:45

jophab

5,356
14
41
60

answered Jun 16 '18 at 14:57

Gordon Linoff

1,242,037
58
646
786

Twice is probably trivial, but 11 would likely be a little more costly. :) Seems the goal posts have changed. – Thom A Jun 16 '18 at 15:15
@Larnu . . . Where did "11" come from? In any case, I upvoted Jobhab's answer, because it is correct. I was just thinking . . . "The OP started with the idea of doing a pivot. There are way better ways of doing this." – Gordon Linoff Jun 16 '18 at 15:30
The comments under the OPs question. They dont actually only have 2 roles, they have 11 (why I mentioned the changing goal posts). But yes, I agree and also upvoted the other answer. – Thom A Jun 16 '18 at 15:35

score 1 · Answer 3 · answered Jun 16 '18 at 15:12

This is a bit of a future proof answer, in case the number of roles you have can change; thus you need to change your SQL. This avoids that need, but does (like Gordan's current answer) require 2 scans of the table:

CREATE TABLE Employee (ID int, [Role] varchar(10), Employee varchar(15));

INSERT INTO Employee
VALUES (1, 'Manager','Steve'),
       (2, 'Consultant','Jayne');

GO
--Hard coded SQL solution

SELECT ID,
       CASE [Role] WHEN 'Manager' THEN Employee END AS Manager,
       CASE [Role] WHEN 'Consultant' THEN Employee END AS Consultant
FROM Employee;
GO
--Now, let's add a another role, and try the SQL again:
INSERT INTO Employee
VALUES (3, 'Director','Sally');
SELECT ID,
       CASE [Role] WHEN 'Manager' THEN Employee END AS Manager,
       CASE [Role] WHEN 'Consultant' THEN Employee END AS Consultant
FROM Employee;
--Unsurprisingly, Sally doesn't appear, but a "blank" row does
GO
--Dynamic SQL solution:
DECLARE @SQL nvarchar(MAX);

SET @SQL = N'SELECT ID,' +NCHAR(10) +
           STUFF((SELECT DISTINCT N',' + NCHAR(10) +
                                  N'       CASE [Role] WHEN ' + QUOTENAME([Role],N'''') + N' THEN Employee END AS ' + QUOTENAME([Role])
                  FROM Employee
                  FOR XML PATH(N'')),1,2,N'') + NCHAR(10) +
           N'FROM Employee;';
PRINT @SQL; --your best friend
EXEC sp_executesql @SQL;
GO

DROP TABLE Employee;

Convert SQL Row values into Columns

3 Answers3