| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
 Posted - 03/21/2002 : 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
Australia
1478 Posts |
Posted - 03/21/2002 : 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" |
 |
|
|
Nazim
A custom title
United Arab Emirates
1408 Posts |
Posted - 03/21/2002 : 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 |
|
|
|
rrb
SQLTeam Poet Laureate
Australia
1478 Posts |
Posted - 03/21/2002 : 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 |
|
|
|
rrb
SQLTeam Poet Laureate
Australia
1478 Posts |
Posted - 03/21/2002 : 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 |
|
|
|
rrb
SQLTeam Poet Laureate
Australia
1478 Posts |
Posted - 03/21/2002 : 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" |
|
|
|
Nazim
A custom title
United Arab Emirates
1408 Posts |
Posted - 03/22/2002 : 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)
--------------------------------------------------------------
|
|
|
|
Arnold Fribble
Yak-finder General
United Kingdom
1961 Posts |
Posted - 03/22/2002 : 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
|
|
|
|
rrb
SQLTeam Poet Laureate
Australia
1478 Posts |
Posted - 03/24/2002 : 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" |
|
|
|
Page47
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 05/07/2002 : 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> |
|
|
| |
Topic |
|
|
|
String Sum
