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)
 Rows TO Columns with SUM Function @ the end of rec
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Yonkouturko
Yak Posting Veteran

Philippines
59 Posts

Posted - 09/25/2013 :  14:34:11  Show Profile  Reply with Quote
First i have this kind of table

ID|DATE|AMOUNT|REMARKS
1 05/17 100 expenses
2 05/18 200 expenses
3 05/17 50 food
4 05/19 400 clothes

then i want to show this view for my grid

05/17 | 05/18 | 05/19
expenses 100 200
food 50
clothes 400

total: 150 200 400


its like im gonna make DATE Records as COLUMN
and make the REMARKS Records as COLUMN
AMOUNT as RECORDS under date and beside(right side) REMARKS
then at the last im gonna do a SUM function for EVERY DATE and put it under the last RECORD

i 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

India
165 Posts

Posted - 09/26/2013 :  05:48:39  Show Profile  Reply with Quote
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)u
PIVOT(SUM(Amount) FOR Date1 IN ([05/17],[05/18],[05/19])) AS pvt
UNION ALL
SELECT 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 TableName


veeranjaneyulu

Edited by - VeeranjaneyuluAnnapureddy on 09/26/2013 08:19:50
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.12 seconds. Powered By: Snitz Forums 2000