Can anyone please help me in adding these values.

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Can anyone please help me in adding these values.

Author

Topic

wshtrue
Yak Posting Veteran

74 Posts

Posted - 2004-10-28 : 21:27:28

Hi Everyone,
I have a table which looks like this but in actual table there would be hundereds of records and i don't want to use cursor so how should i solve this problem
TABLE:
value|ID
12 |A
23 |A
22 |B
11 |B
55 |A
77 |C

Output should look like this
ID | Total
A |12+23+55=90
B |22+11=33
C |77
It should add all those values where ID is same.These values will not be hardcoded.I just gave you an example.Hope i am clear.

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2004-10-28 : 21:33:21

SELECT ID, SUM(value) AS Total
FROM Table
GROUP BY ID

Results:
ID | Total
A | 90
B | 33
C | 77

Andy

wshtrue
Yak Posting Veteran

74 Posts

Posted - 2004-10-28 : 21:38:05

Thanks Andi! My requirement is to show as 12+23+55=90 as total not just the total 90.You know what i am saying .I mean its giving me total but total of which values.Hope i am clear. Thanks

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2004-10-28 : 21:57:51

This should be dealt with in your app/presentation layer

This link should help you get started if you want to go down this route
[url]http://www.sqlteam.com/item.asp?ItemID=11021[/url]

Andy

wshtrue
Yak Posting Veteran

74 Posts

Posted - 2004-10-28 : 22:02:47

Thanks Andi! I really appreciate your help.I am going to read the link you just sent me.Thanks

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-28 : 22:08:52

try this out:


CREATE FUNCTION dbo.concatStr
(
	@curStr varchar(4000),
	@newStr varchar(100)
)  
RETURNS varchar(4000) AS  
BEGIN 
	Return (isnull(@curStr+',','')+@newStr)
END
Go

Declare @myTable table (id varchar(100), val int, display varchar(4000))
Insert Into @myTable
Select 'A', 12, null
Union Select 'A', 23, null
Union Select 'B', 22, null
Union Select 'B', 11, null
Union Select 'A', 55, null
Union Select 'C', 77, null

Declare @curStr varchar(4000),
	@key varchar(4000)

Update @myTable
Set 
	@curStr = display = case when @key = convert(varchar,id) then dbo.concatStr(@curStr,convert(varchar,val)) else convert(varchar,val) end,
	@key = id
From @myTable

Select id, replace(display,',','+') + '=' + (Select convert(varchar,sum(val)) From @myTable Where id = A.id)
From @myTable A
where len(display) = (select max(len(display)) from @myTable where id = A.id)

go

Drop function dbo.concatStr

Corey

wshtrue
Yak Posting Veteran

74 Posts

Posted - 2004-10-28 : 22:15:41

Corey,
Refer to your code
Select 'A', 12, null
Union Select 'A', 23, null
Union Select 'B', 22, null
Union Select 'B', 11, null
Union Select 'A', 55, null
Union Select 'C', 77, null
Corey, right now i have 7 records only so you know those values but how would i be putting these values when there will be hundred of reocrds.Which values will i be putting in that situation.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-28 : 22:19:57

Oh... I was simply setting up the test data. You can put in any data you want, and it will convert it according to your defined rule: val1 + val2 + ... + valN = totalVal

if you have your data in a table called myData, you would populate the temp table with


Declare @myTable table (id varchar(100), val int, display varchar(4000))
Insert Into @myTable
Select id, total from myData

Declare @curStr varchar(4000),
	@key varchar(4000)

Update @myTable
Set 
	@curStr = display = case when @key = convert(varchar,id) then dbo.concatStr(@curStr,convert(varchar,val)) else convert(varchar,val) end,
	@key = id
From @myTable

Select id, replace(display,',','+') + '=' + (Select convert(varchar,sum(val)) From @myTable Where id = A.id)
From @myTable A
where len(display) = (select max(len(display)) from @myTable where id = A.id)

Corey

wshtrue
Yak Posting Veteran

74 Posts

Posted - 2004-10-28 : 22:25:11

Thanks Corey! I really appreciate your help. I will let you know once i try it.Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources