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 2000 Forums
 SQL Server Development (2000)
 String Sum
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 03/21/2002 :  00:27:11  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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

Australia
1479 Posts

Posted - 03/21/2002 :  01:37:11  Show Profile  Reply with Quote
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

United Arab Emirates
1408 Posts

Posted - 03/21/2002 :  03:25:30  Show Profile  Reply with Quote
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

Australia
1479 Posts

Posted - 03/21/2002 :  17:40:23  Show Profile  Reply with Quote
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

Australia
1479 Posts

Posted - 03/21/2002 :  19:12:56  Show Profile  Reply with Quote
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

Australia
1479 Posts

Posted - 03/21/2002 :  19:18:08  Show Profile  Reply with Quote
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

United Arab Emirates
1408 Posts

Posted - 03/22/2002 :  02:48:51  Show Profile  Reply with Quote
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

United Kingdom
1961 Posts

Posted - 03/22/2002 :  05:11:45  Show Profile  Reply with 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:

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

Australia
1479 Posts

Posted - 03/24/2002 :  18:51:37  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 05/07/2002 :  13:24:39  Show Profile  Reply with Quote
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
  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.11 seconds. Powered By: Snitz Forums 2000