Hi,

I am storing transaction data for users vertically as I have shown below in table1.

And now I need to get the data for whole month horizontally as I have shown below in table2.

Please let me know how to write query for same ?

Table1:-
user	Date 	trans
a	1/1/2010	10
a	1/1/2010	10
b	1/1/2010	20
b	1/1/2010	20
a	2/1/2010	30
b	2/1/2010	40
b	4/1/2010	60


Table2:-
user	1/1/2010	2/1/2010	3/1/2010	4/1/2010	5/1/2010	6/1/2010	7/1/2010	8/1/2010	9/1/2010
a	20	30	0	60	0	0	0	0	0
b	40	40	0	0	0	0	0	0	0


I want data for whole month (till last day of the month) as shown above.

Regards,
harsha.


hi,
you need to use PIVOT table in sql server
refer :  http://technet.microsoft.com/en-us/library/ms177410.aspx
http://www.simple-talk.com/sql/t-sql-programming/creating-cross-tab-queries-and-pivot-tables-in-sql/
http://blog.sqlauthority.com/2008/05/22/sql-server-pivot-table-example/


ELECT * FROM Orders
 PIVOT (COUNT(employeeID) FOR employeeID IN ([1],[2],[3],[4],[5],[6],[7],[8],[9])) p

http://technet.microsoft.com/en-us/library/ms177410.aspx
visit my Blog Student Academic Blog 

Please Mark as Answered If its helpful you.




Aamir,
Thanks for the reply.
In my problem I can't write like [1],[2],[3],[4],[5],[6],[7],[8],[9]... Because this should by dynamic. I will pass only month and year for the query. It has to give me data in the above format for whole month.
Regards,
Harsha.



You should use dynamic query with pivot...
check below small sample...
You should use dynamic query with pivot...
check below small sample...
view plaincopy to clipboardprint?
1.	Create Table #Table1  
2.	(  
3.	    users varchar(10),  
4.	    Date datetime,  
5.	    trans int  
6.	)  
7.	insert into #Table1  
8.	Select 'a','1/1/2010',10  
9.	Union All  
10.	Select 'a','1/1/2010',10  
11.	Union All  
12.	Select 'b','1/1/2010',20  
13.	Union All  
14.	Select 'b','1/1/2010',20  
15.	Union All  
16.	Select 'a','1/2/2010',30  
17.	Union All  
18.	Select 'b','1/2/2010',40  
19.	Union All  
20.	Select 'b','1/4/2010',60  
21.	  
22.	DECLARE @SqlQuery nVARCHAR(MAX)    
23.	DECLARE @Date VARCHAR(MAX)    
24.	set @Date=''  
25.	SET @SqlQuery = ''    
26.	  
27.	  
28.	Select @Date=@Date+','+Date  
29.	From  
30.	(  
31.	    Select Distinct '['+Convert(Char(10),Date,101)+']' As Date  
32.	    From #Table1  
33.	) As AA  
34.	          
35.	Set @Date=substring(@Date,2,len(@Date))  
36.	  
37.	    SET @SqlQuery ='SELECT *    
38.	    FROM    
39.	    (    
40.	        SELECT     
41.	Distinct  
42.	            users    
43.	            , (    
44.	                Convert(Char(10),Date,101)    
45.	              ) AS date1    
46.	            , trans    
47.	        FROM #Table1  
48.	    ) AS ItemDetail    
49.	    PIVOT ( SUM(trans) FOR date1 IN ( ' + @Date + ' ) ) AS pvt '  
50.	  
51.	  
52.	print @SqlQuery  
53.	  
54.	EXECUTE ( @SqlQuery )  
55.	  
56.	--Output  
57.	--users 01/01/2010 01/02/2010 01/04/2010  
58.	----------------------------------------  
59.	--a     10          30          NULL  
60.	--b     20          40          60  
61.	  
62.	  
63.	  
64.	drop table #Table1  


Hi Yogesh,
Thank you very much for the reply.
I was running this query on Oracle. It is giving some errors on Oracle. Please let me know what changes I need to do so that It can run on Oracle.

Regards,
Harsha.



•	You may want to ask this question in the Oracle forum then. Looking for a job opportunity. 
•	 
31,665 point All-Star
•	Naom 
•	Member since 12-31-2007
•	Wisconsin
•	Posts 6,995
b