T-SQL (Sql Server 2008)  to output rows to columns by joinging base table

kishan66 used Ask the Experts™
I have two tables…… RoleMasterTable & TeamTable … and I want to combine them to third table TeamRoleTable using sql (Sql Server 2008)
... pls let me know how do i do that

RoleID          Role
1      Manager
2      Lead
3      Member
  MANAGER               LEAD                  Member
Chris John      Keith Silverman      MIchel Jim
Chris John      Keith Silverman      Nick Kanon
Chris John      Mark Weith      Tom Thomb
Chris John      Harry Poter      Zach Hassle

OutPut to TeamRoleTable should be ……

RoleID            Name
1                    Chris John
2                  Keith Silverman
2                 Mark Weith
2                Harry Poter
3                 MIchel Jim
3      Nick Kanon
3      Tom Thomb
3      Zach Hassle

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
how many roles you have? From the above example you could just do the following:

select       distinct
from (
select       Manager as [1],
      Lead as [2],
      Member as [3]
from TeamTable
) o
unpivot (Name for RoleID in (1, 2, 3)) p
It should be like below:

insert into TeamRoleTable
select distinct RoleID, case when RoleID = 1 then MANAGER
                               when RoleID = 2 then LEAD
                               when RoleID = 2 then Member
  from RoleMasterTable RMT, TeamTable TT
Of course to insert them into another table:

insert TeamRoleTable
select       distinct
from (
select       Manager as [1],
      Lead as [2],
      Member as [3]
from TeamTable
) o
unpivot (Name for RoleID in (1, 2, 3)) p 

Open in new window


That was fast.....
It works ..

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial