quote:

---

Originally posted by lio1972

Thanks
It works perfect. I really appreciate your help.
Is there a way to pass the date parameters?
Let say that I have 100 dates and I want to pass a date from certain week and display the 7 days of that week.

---

select * from (select Tab1.Name,Tab2.Company, Tab2.employeeNum,Tab2.Date,Tab2.Hours from Tab2 inner join Tab1 
ON Tab1.Company = Tab2.Company AND Tab2.Emp = Tab2.Emp )x
pivot
(
sum(hours) for ClockInDate in (select Convert(Date, Date) from Tab2 )
)p

select * from (select Tab1.Name,Tab2.Company, Tab2.employeeNum,Tab2.Date,Tab2.Hours from Tab2 inner join Tab1 
ON Tab1.Company = Tab2.Company AND Tab2.Emp = Tab2.Emp )x
pivot
(
sum(hours) for Date in (select '[' + Convert(Varchar(10),Date,110) +']' from Tab2 )
)p

Declare @dates Table(daysInWeek nvarchar(12));
SET @dates = STUFF ((select '[' + Convert(Varchar(10),Date,110) +']' from Tab2),1,1,'')
select * from (select Tab1.Name,Tab2.Company, Tab2.employeeNum,Tab2.Date,Tab2.Hours from Tab2 inner join Tab1 
ON Tab1.Company = Tab2.Company AND Tab2.Emp = Tab2.Emp )x
pivot
(
sum(hours) for Date in (@dates)
)p

quote:

---

Originally posted by lio1972

Reading further about Dynamic Pivot I decided to do that, but the errors were that @dates must be scalar variable:

Declare @dates varchar(3000)Table(daysInWeek nvarchar(12));
DECLARE @SQL varchar(max)
SET @dates = STUFF ((select '[' + Convert(Varchar(10),Date,110) +']' from Tab2),1,1,'')
SET @SQL='select * from (select Tab1.Name,Tab2.Company, Tab2.employeeNum,Tab2.Date,Tab2.Hours from Tab2 inner join Tab1 
ON Tab1.Company = Tab2.Company AND Tab2.Emp = Tab2.Emp )x
pivot
(
sum(hours) for Date in (' + @dates + ')
)p'
EXEC(@SQL)

---