CODEWITHSUNDEEP
sqlsql-server
user1783170
user1783170

Reputation: 113

Convert SQL Row values into Columns

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

Query Result

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?

Expected Output

Upvotes: 0

Views: 81

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

`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().

Upvotes: 1

jophab
jophab

Reputation: 5529

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

Upvotes: 3

Thom A
Thom A

Reputation: 95989

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;

Upvotes: 1

Related Questions