May 23, 2013 at 6:50 am
DECLARE @EmpID nvarchar(max) = ' '
DECLARE @sql nvarchar(max)
SELECT @EmpID = @EmpID + '['+Convert(nvarchar(max),EmpID) +']'
FROM (SELECT DISTINCT EmpID FROM emp.teamassignments) AS StaffList
SET @EmpID = STUFF(@EmpID,1,1,'')
PRINT @EmpID
SET @sql = 'SELECT * FROM emp.teamassignments '
+'PIVOT TEAM FOR EmpID IN
''['+@EmpID+']'' AS P'
EXECUTE sp_executesql @sql
I am trying to pivot at get a listing of my EmpID's going down and their respective teams across.
Some belong to 1 team others may belong to 5 or more.
I have this:
EmpID TEAM
1 Stripes
1 Orange
1 Green
2 Blue
3 Sales
3 Exec
I would like this:
EmpID TEAM_1 TEAM_2 TEAM_3
1 Stripes Orange Green
2 Blue None None
3 Sales Exec None
May 23, 2013 at 7:39 am
This link was shared in a similar post earlier today. I think it is applicable to your situation as well.
http://www.sqlservercentral.com/articles/comma+separated+list/71700/
The link above will show you how to list the teams next to each ID number in a comma delimited list. If you truly want separate columns, then the number of teams has to be a fixed quantity. Before I go into details on a solution that might not be needed, let me as you this: Is the number of teams a fixed number? And do you want separate columns, or is a comma delimited list satisfactory?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply