Create Table dbo.EmpProj 
( EmpID int,
  ProjectID int,
  ProjRole varchar(1000),
  HoursWorked int
 )
 
Insert into EmpProj Values(1, 1, 'Create Etl', 85) 
Insert into EmpProj Values(1, 2, 'Analyze Data', 20) 
Insert into EmpProj Values(1, 3, 'Create Update Processes', 120) 
Insert into EmpProj Values(2, 5, 'QA', 30) 
Insert into EmpProj Values(2, 1, 'Test Scripts', 25) 
Insert into EmpProj Values(2, 7, 'Test ETL', 16) 
Insert into EmpProj Values(3, 2, 'Create Documents', 40) 
 
 Create Table dbo.Project
 ( ProjectID int, 
   ProjectName varchar(1000)
  )
  
Insert into Project Values(1, 'ABC Project')
Insert into Project Values(2, 'AAA Project')
Insert into Project Values(3, 'XYZ Project')
Insert into Project Values(4, 'Test Project')
Insert into Project Values(5, 'OOO Project')
Insert into Project Values(6, 'PETProject')
Insert into Project Values(7, 'AlB Project')
Insert into Project Values(8, 'ZXW Project')

quote:

---

Originally posted by visakh16

see

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

Create Table #EmpProj 
( EmpID int,
  ProjectID int,
  ProjRole varchar(1000),
  HoursWorked int
 )
 
Insert into #EmpProj Values(1, 1, 'Create Etl', 85) 
Insert into #EmpProj Values(1, 2, 'Analyze Data', 20) 
Insert into #EmpProj Values(1, 3, 'Create Update Processes', 120) 
Insert into #EmpProj Values(2, 5, 'QA', 30) 
Insert into #EmpProj Values(2, 1, 'Test Scripts', 25) 
Insert into #EmpProj Values(2, 7, 'Test ETL', 16) 
Insert into #EmpProj Values(3, 2, 'Create Documents', 40) 
 
 Create Table #Project
 ( ProjectID int, 
   ProjectName varchar(1000)
  )
  
Insert into #Project Values(1, 'ABC Project')
Insert into #Project Values(2, 'AAA Project')
Insert into #Project Values(3, 'XYZ Project')
Insert into #Project Values(4, 'Test Project')
Insert into #Project Values(5, 'OOO Project')
Insert into #Project Values(6, 'PETProject')
Insert into #Project Values(7, 'AlB Project')
Insert into #Project Values(8, 'ZXW Project')

select t.EmpID
,max(case when seq = 1 then ProjectName else null end) as [ProjectName_1]
,max(case when seq = 1 then ProjRole else null end) as [ProjectRole_1]
,max(case when seq = 1 then HoursWorked else null end) as [HoursWorked_1]
,max(case when seq = 2 then ProjectName else null end) as [ProjectName_2]
,max(case when seq = 2 then ProjRole else null end) as [ProjectRole_2]
,max(case when seq = 2 then HoursWorked else null end) as [HoursWorked_2]
,max(case when seq = 3 then ProjectName else null end) as [ProjectName_3]
,max(case when seq = 3 then ProjRole else null end) as [ProjectRole_3]
,max(case when seq = 3 then HoursWorked else null end) as [HoursWorked_3]
from
(
select row_number() over(partition by a.EmpID order by b.ProjectName) as seq, a.EmpID, b.ProjectName,a.ProjRole,a.HoursWorked from #EmpProj a
inner join #Project b on a.ProjectID = b.ProjectID
) t
group by t.EmpID

EmpID       ProjectName_1        ProjectRole_1             HoursWorked_1 ProjectName_2        ProjectRole_2             HoursWorked_2 ProjectName_3        ProjectRole_3             HoursWorked_3
----------- -------------------- ------------------------- ------------- -------------------- ------------------------- ------------- -------------------- ------------------------- -------------
1           AAA Project          Analyze Data              20            ABC Project          Create Etl                85            XYZ Project          Create Update Processes   120
2           ABC Project          Test Scripts              25            AlB Project          Test ETL                  16            OOO Project          QA                        30
3           AAA Project          Create Documents          40            NULL                 NULL                      NULL          NULL                 NULL                      NULL

quote:

---

Originally posted by GhantaBro

Vijay thanks...this works well with the data we have, but the ProjectID will be dynamic... it will grow for some. I need to create the columns dynamically...

---

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by GhantaBro

Vijay thanks...this works well with the data we have, but the ProjectID will be dynamic... it will grow for some. I need to create the columns dynamically...

---

thats what i've provided you with. the link shows how to do cross tabbing dynamic based on column values. You need to apply somekind of aggregation function even if you're not looking for summary here as cross tabbing itself is an aggregation operation.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---