asked on

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

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

RoleMasterTable
RoleID        Role
1      Manager
2      Lead
3      Member

TeamTable
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 ……

TeamRoleTable
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

Thanks

SOLUTION

ralmada

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

alpmoon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ralmada

Of course to insert them into another table:

insert TeamRoleTable
select       distinct
      RoleID,
      Name
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

kishan66

ASKER

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