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.
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#11 Value#21 Value#32 Value#12 Value#23 Value#1---------------------I need the following result :----------------------------i SumC ---------- ----------------1 Value#1+Value#2+Value#32 Value#1+Value#23 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 thisselect i, sum(cast(c as float))from Testgroup by iorder 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
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 |
|
|
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 timetryselect 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....hmmmEdited by - rrb on 03/21/2002 17:41:57 |
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-03-21 : 19:12:56
|
OK SanjeevIf 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,NULLfrom Testorder by i,cdeclare @sList nvarchar(1000)Declare @sLast nvarchar(50)declare @sedge nvarchar(50)set @sList = ''set @sLast =''set @sedge = ''update #aset @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 endselect i, max(list) from #agroup by idrop table #aWOW - sorry but I gotta say I'm excited by that one!PS - THANKS GRAZEdited by - rrb on 03/21/2002 19:15:47 |
|
|
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 youcreate 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,NULLfrom #border by i,cdeclare @sList nvarchar(1000)Declare @sLast nvarchar(50)declare @sedge nvarchar(50)set @sList = ''set @sLast =''set @sedge = ''update #aset @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 endselect i, max(list) from #agroup by idrop table #adrop 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
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 timetryselect cast('1.2' as float)
-------------------------------------------------------------- |
|
|
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 #aset @sList = list = case when @sLast <> i then c else @sList + '+' + c end, @sLast = i |
|
|
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 quibblewith 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
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 oncreate 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,NULLfrom #b order by i,j,cselect * from #adeclare @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 #aselect i,j, max(list) from #a group by i,j drop table #a drop table #b <O> |
|
|
|
|
|
|
|