Constraint Violating Yak Guru
Posted - 01/07/2014 : 20:22:54
I have this working pivot script from previous post http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=189893
DECLARE @DateList varchar(5000), @SQL varchar(max), @batchid uniqueidentifier
set @batchid = '460f74fc-c318-4152-b758-4afa1db9c890'
SELECT @DateList = STUFF((SELECT DISTINCT ',[' + Dates + ']'
FROM View_2 where View_2.BatchID = @batchid
ORDER BY ',[' + Dates + ']'
FOR XML PATH('')),1,1,'')
SET @SQL='SELECT *
FROM View_2 t
PIVOT (Max(t.empty) FOR t.Dates IN (' + @DateList + '))p
where p.batchid = '''+ cast(@batchid as varchar(100))+ ''''
The problem i have is i want to show 5 columns in each reports page.
For example, if i have 3 dates, naturally, it would have 3 columns in a pivot report but i want to display additional 2 empty date columns. In other word, i want to show groups of 5 date columns in a report.
So if i have dates in my result set that is not in groups of 5, i want add additional empty date columns to make it to 5.
How should i go about it.
many thanks in advance.