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 |
|
Yonkouturko
Yak Posting Veteran
59 Posts |
 Posted - 2013-09-25 : 14:34:11
|
| First i have this kind of tableID|DATE|AMOUNT|REMARKS1 05/17 100 expenses2 05/18 200 expenses3 05/17 50 food4 05/19 400 clothesthen i want to show this view for my grid 05/17 | 05/18 | 05/19expenses 100 200 food 50 clothes 400total: 150 200 400 its like im gonna make DATE Records as COLUMNand make the REMARKS Records as COLUMN AMOUNT as RECORDS under date and beside(right side) REMARKSthen at the last im gonna do a SUM function for EVERY DATE and put it under the last RECORDi hope i make my explanation clear on thiAs matter!!! thanks in advance!!!!!any suggestion will do... I THINK im an intermediate level in SQL program but there are things i really don't know how to do..thats why i ask for opinion or if its possible, for answers... again THANK YOU SQLTEAM!!!! thanks in advance for people who will suggest/answer to this post |
|
|
VeeranjaneyuluAnnapureddy
Posting Yak Master
169 Posts |
Posted - 2013-09-26 : 05:48:39
|
| CREATE TABLE TableName(ID INT,DATE1 VARCHAR(10),AMOUNT INT,REMARKS VARCHAR(13))INSERT INTO TableName VALUES(1,'05/17',100,'expenses'),(2,'05/18',200,'expenses'),(3,'05/17',50,'food'),(4,'05/19',400,'clothes')SELECT Remarks,[05/17],[05/18],[05/19] FROM(SELECT DISTINCT Date1,Amount,Remarks FROM TableName)uPIVOT(SUM(Amount) FOR Date1 IN ([05/17],[05/18],[05/19])) AS pvtUNION ALLSELECT DISTINCT 'Total',(SELECT SUM(Amount) FROM TableName WHERE Date1 = '05/17') AS '05/17',(SELECT SUM(Amount) FROM TableName WHERE Date1 = '05/18')AS '05/18',(SELECT SUM(Amount) FROM TableName WHERE Date1 = '05/19')AS '05/19'FROM TableNameveeranjaneyulu |
 |
|
|
|
|
|
|
|
Rows TO Columns with SUM Function @ the end of rec