hello, Lets look this Proc :
set nocount on
create table Histo
(
Id varchar(10)primary key,
Week varchar(10),
Project varchar(10)
)
insert into Histo (Id, Week, Project)
select '47','2006-12','Internet'
union all
select '48','2006-13','Internet'
union all
select '49','2006-12','Intranet'
go
select*from Histo
go
SELECT
Project,[2006-12],
[2006-13]
FROM
(SELECT Project, week, id
FROM histo) s
PIVOT
(
max(id)
FOR Week IN ([2006-12],[2006-13])
) p
ORDER BY [Project]
go
drop
table histothere are 2 blocks of data "hard coded" ([2006-12], [2006-13]) and I want to have something more elegant.
I want to be able to set a begin week and a number of weeks displayed.
Can anyone help me Thanks a lot
complex SQL (for me) step 2
Bennie9425
No help for you there. These value have to be hard coded in the query to work. You will need to use dynamic SQL to build your statement to make it more elegant (well, when speaking of dynamic SQL, elegant is clearly not the right word)
It shouldn't be too hard to dynamically generate the date blocks '[2006-12],[2006-13]', so it might be reasonable.
Go here to vote/discuss on this feature so Microsoft will hear us :)http://lab.msdn.microsoft.com/ProductFeedback/viewfeedback.aspx feedbackid=6f168645-a348-4644-b369-63849d5b8355
TimHatton