July 15, 2012 at 8:18 am
Hi
i have a table which something like this :
Tests(TestID int, Col1 money, Col2 nvarchar(50), Col3 int, RegsiterDate char(10))
Now, i want to get pivot report based on the 'RegisterDate' column like this :
2012/01/05 2012/01/10 2012/01/15
Col1 12.5 14.5 15.5
Col2 Text1 Text2 Text3
Col3 5 6 7
How can i do this ?
thanks in advance
July 16, 2012 at 5:22 am
First of all, it would have been more helpful if you had added a few rows of data in your example. However, if you want a pivot kind of report/output, you can use the "Sum(CASE fieldname WHEN '07/2012' THEN fieldtobesumed ELSE 0) and 'July' , Sum(CASE fieldname WHEN '08/2012' THEN fieldtobesumed ELSE 0) and 'August' , Sum(CASE fieldname WHEN '09/2012' THEN fieldtobesumed ELSE 0) and 'September' ,........) that will create a table with July, August, September as columns and values of fieldtobesumed as cell values. Also google on 'pivot sql server'. You should get more information.
July 16, 2012 at 5:34 am
Sorry in my previous text I made a mistake. It should be "as July" and not "and July".
First of all, it would have been more helpful if you had added a few rows of data in your example. However, if you want a pivot kind of report/output, you can use the "Sum(CASE fieldname WHEN '07/2012' THEN fieldtobesumed ELSE 0) as 'July' , Sum(CASE fieldname WHEN '08/2012' THEN fieldtobesumed ELSE 0) as 'August' , Sum(CASE fieldname WHEN '09/2012' THEN fieldtobesumed ELSE 0) as 'September' ,........) that will create a table with July, August, September as columns and values of fieldtobesumed as cell values. Also google on 'pivot sql server'. You should get more information
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply