Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sg2255551
Constraint Violating Yak Guru
274 Posts |
 Posted - 2014-01-07 : 20:22:54
|
| hiI have this working pivot script from previous post http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=189893DECLARE @DateList varchar(5000), @SQL varchar(max), @batchid uniqueidentifierset @batchid = '460f74fc-c318-4152-b758-4afa1db9c890'SELECT @DateList = STUFF((SELECT DISTINCT ',[' + Dates + ']'FROM View_2 where View_2.BatchID = @batchidORDER BY ',[' + Dates + ']'FOR XML PATH('')),1,1,'')SET @SQL='SELECT *FROM View_2 tPIVOT (Max(t.empty) FOR t.Dates IN (' + @DateList + '))pwhere p.batchid = '''+ cast(@batchid as varchar(100))+ ''''EXEC(@SQL)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. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-01-08 : 06:42:21
|
| do you've a calendartable in your database? for your above requirement you need to start from calendar table to display 5 dates irespective of whether it has the data or not. If you dont have one you can use function below to create it on the flyhttp://visakhm.blogspot.com/2010/02/generating-calendar-table.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
|
|
|
|
|
Even Pivot Columns