SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Even Pivot Columns
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sg2255551
Constraint Violating Yak Guru

272 Posts

Posted - 01/07/2014 :  20:22:54  Show Profile  Reply with Quote
hi

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))+ ''''

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

India
52317 Posts

Posted - 01/08/2014 :  06:42:21  Show Profile  Reply with Quote
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 fly
http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000