|
|
Posted by PDreyer on 07/10/07 12:45
On Jun 21, 8:44 pm, Apaxe <apaxe2000Milh...@gmail.com> wrote:
> In the database i have a table with this information:
>
> key_id => 1
> key_desc => 43+34+22+12
>
> I want sum the values in key_desc. Something like:
>
> SELECT key_desc FROM table
>
> But the result of the select was "111" and not "43+34+22+12".
>
> Is this posible?
This looks similar to one of your other problems see
http://groups.google.com/group/microsoft.public.excel/browse_thread/thread/4964397f4e248e1d/0f4b73956cc1b7f8#0f4b73956cc1b7f8
How come you did not clean the data before it got into the database?
Assuming max 4 values (columns) to sum
CREATE TABLE Test(key_id int, key_desc varchar(100))
INSERT INTO Test SELECT 1, '43+34+22+12' UNION SELECT 2,'34+56+78'
select
convert(int,isnull(PARSENAME(replace(key_desc,'+','.'),4),0))
+convert(int,isnull(PARSENAME(replace(key_desc,'+','.'),3),0))
+convert(int,isnull(PARSENAME(replace(key_desc,'+','.'),2),0))
+convert(int,isnull(PARSENAME(replace(key_desc,'+','.'),1),0))
from Test
or else you can do:
declare c1 cursor for select convert(varchar(9),key_id),key_desc from
Test
open c1
declare @id int, @idc varchar(9), @key_desc varchar(100)
select key_id, 0 'total' into #t1 from Test where 1=2
fetch c1 into @idc, @key_desc
while @@FETCH_STATUS = 0
begin
exec ( 'insert into #t1 select key_id, '+@key_desc+' from Test where
key_id='+@idc)
fetch c1 into @idc, @key_desc
end
close c1
select * from #t1
drop table #t1
deallocate c1
drop TABLE Test
Navigation:
[Reply to this message]
|