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
 SQL Server Development (2000)
 String Sum

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-21 : 00:27:11
Sanjeev writes "Hi,

I have table test having following values:
------------------------------------------
Here i is int and c is varchar data type
-----------------------------------------
select i,c from Test
-------------------------------------------
i           c          
----------- ----------
1 Value#1
1 Value#2
1 Value#3
2 Value#1
2 Value#2
3 Value#1
---------------------
I need the following result :
----------------------------
i SumC
---------- ----------------
1 Value#1+Value#2+Value#3
2 Value#1+Value#2
3 Value#1

------------------------------
How to do this ? Is above result can be done in a single SQL statement ? Pls help me.
------------------------------------
Sanjeev"

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-21 : 01:37:11
Perhaps I misunderstand you - you're saying SUM but do you mean
CONCATENATE?

If you mean SUM then you want this
select i, sum(cast(c as float))
from Test
group by i
order by i

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"
Go to Top of Page

Nazim
A custom title

1408 Posts

Posted - 2002-03-21 : 03:25:30
I dont think you can cast a string into float.

Sanjeev, is it a homework question or what ?. i fail to understand what possibly you are trying to do with it. Can you explain?.





--------------------------------------------------------------


Edited by - Nazim on 03/21/2002 03:26:15
Go to Top of Page

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-21 : 17:40:23
quote:

I dont think you can cast a string into float.



Nazim, I'm not sure what you mean - I do this all the time

try
select cast('1.2' as float)

...works for me.

What I was trying to get at was whether he has numbers stored as strings that he wants "SUM"med or whether he wants strings CONCATENATED....?

To add to the confusion, the items are called "Value1" etc....hmmm

Edited by - rrb on 03/21/2002 17:41:57
Go to Top of Page

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-21 : 19:12:56
OK Sanjeev

If this isn't the best answer you'll ever get - (even if I do say so myself) - then my name isn't William Jefferson Clinton.

create table #a (i int, c nvarchar(50), list nvarchar(1000), lastval nvarchar(50), edge nvarchar(50))
insert into #a (i,c,list,lastval, edge)
select i,c,NULL,NULL,NULL
from Test
order by i,c

declare @sList nvarchar(1000)
Declare @sLast nvarchar(50)
declare @sedge nvarchar(50)
set @sList = ''
set @sLast =''
set @sedge = ''

update #a
set @sedge= edge = case when @sLast <> i then 1 else 0 end,
@slast = lastval = i,
@slist = list = case when @sedge = 1 then c else @slist + '+' + c end

select i, max(list) from #a
group by i

drop table #a


WOW - sorry but I gotta say I'm excited by that one!

PS - THANKS GRAZ

Edited by - rrb on 03/21/2002 19:15:47
Go to Top of Page

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-21 : 19:18:08
In case you want to try it - here's some dummy data for you
create table #b (i int, c nvarchar(50))
insert into #b select 1,'Value#1'
insert into #b select 1,'Value#2'
insert into #b select 1,'Value#3'
insert into #b select 1,'Value#4'
insert into #b select 2,'Value#1'
insert into #b select 2,'Value#2'
insert into #b select 3,'Value#1'
insert into #b select 3,'Value#2'
insert into #b select 3,'Value#3'

create table #a (i int, c nvarchar(50), list nvarchar(1000), lastval nvarchar(50), edge nvarchar(50))
insert into #a (i,c,list,lastval, edge)
select i,c,NULL,NULL,NULL
from #b
order by i,c

declare @sList nvarchar(1000)
Declare @sLast nvarchar(50)
declare @sedge nvarchar(50)
set @sList = ''
set @sLast =''
set @sedge = ''

update #a
set @sedge= edge = case when @sLast <> i then 1 else 0 end,
@slast = lastval = i,
@slist = list = case when @sedge = 1 then c else @slist + '+' + c end

select i, max(list) from #a
group by i

drop table #a

drop table #b


--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"
Go to Top of Page

Nazim
A custom title

1408 Posts

Posted - 2002-03-22 : 02:48:51
rrb, I dont think he will always store numeric values in a string. so , my alarm was for the data which isnt numeric.

eg: select cast('Sqlteam' as float) -- i dont think this will make sense and wouldnt work too.


quote:

Nazim, I'm not sure what you mean - I do this all the time

try
select cast('1.2' as float)




--------------------------------------------------------------
Go to Top of Page

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-03-22 : 05:11:45
Bravo Rob! Does it work? What's guaranteeing that the rows get updated in an order than presents all the rows with the same i together?
And aren't edge and lastval surplus to requirements:

update #a
set @sList = list = case when @sLast <> i then c else @sList + '+' + c end, @sLast = i



Go to Top of Page

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-24 : 18:51:37
quote:

Bravo Rob! Does it work? What's guaranteeing that the rows get updated in an order than presents all the rows with the same i together?
And aren't edge and lastval surplus to requirements:



All these hard questions! I'm not a guru - just a very naughty boy!

It works alright - I'm not sure about the ordering - I order the values into the temp table - temp tables always appear to hold their order....

However, as for the edge and lastval being surplus - they probably are, but I thought it might make it clearer HOW the code worked!

However, I've never been one to quibble
with the likes of that Arnold Fribble


Cheers - let me learn on.

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"
Go to Top of Page

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-07 : 13:24:39
I just wanted to note that this brilliant piece of code can be adapted for tables with a composite key . . .

set nocount on
create table #b (i int, j int, c nvarchar(50))
insert into #b select 1,1,'Value#1'
insert into #b select 1,1,'Value#2'
insert into #b select 1,2,'Value#3'
insert into #b select 1,2,'Value#4'
insert into #b select 2,2,'Value#1'
insert into #b select 2,2,'Value#2'
insert into #b select 3,1,'Value#1'
insert into #b select 3,3,'Value#2'
insert into #b select 3,3,'Value#3'

create table #a (i int, j int, c nvarchar(50), list nvarchar(1000), lasti nvarchar(50), lastj nvarchar(50), edge nvarchar(50))
insert into #a (i,j,c,list,lasti,lastj,edge)
select i,j,c,NULL,NULL,NULL,NULL
from #b
order by i,j,c

select * from #a

declare @sList nvarchar(1000)
Declare @sLasti nvarchar(50)
Declare @sLastj nvarchar(50)
declare @sedge nvarchar(50)
set @sList = ''
set @sLasti =''
set @sLastj =''
set @sedge = ''

update #a
set @sedge= edge = case when @sLasti <> i or @sLastj <> j then 1 else 0 end,
@slasti = lasti = i,
@slastj = lastj = j,
@slist = list = case when @sedge = 1 then c else @slist + '+' + c end

select * from #a

select i,j, max(list) from #a
group by i,j

drop table #a

drop table #b


<O>
Go to Top of Page
   

- Advertisement -