January 31, 2006 at 9:44 pm
Hi all,
Iam new for sql server.i have small doubt on this,can u people suggest me .....
I want to find sum of the row for all records in one table i.e after summing one record it will be move to the second row and onwards by using cursors and one more thing is that the columns will be dynomic for this table.
Thanks Inadvance,
Rao Aregaddan.
February 1, 2006 at 6:29 am
Is this what you are looking for?
select col1 + col2 + col3 + col4 + col5 from table
Otherwise, please give us a small example with data and the expected output of the query.
February 1, 2006 at 9:16 pm
Hi,
Yes,Exactly i need like that only.But the columns are dynomic and i want to find for all records .Please give me a example by using cursors.....
Thanks Inadvance,
Rao Aregaddan.
February 1, 2006 at 9:17 pm
Hi,
Yes,Exactly i need like that only.But the columns are dynomic and i want to find for all records .Please give me a example by using cursors.....
Thanks Inadvance,
Rao Aregaddan.
February 3, 2006 at 2:55 pm
The first thing is that if your structure is as you say, you aren't taking full advantage of what a Relational DB is all about. Anyway, if you can't change the table structure at this point...
You will need to find a way to recognize the fields you need. Looks like you already have that figured out if you are thinking about using a cursor. If not, it will need to be something common in the name or perhaps you can analyze the colids of the fields and see some pattern or criteria. I worked up an example based on common field names. Doesn't use cursors (I'm currently learning/practicing this technique) You would need to work this into a procedure or thing for your own needs
create table testAddAcrossFields
(field1 int, field2 int, field3 varchar(4),
u1 int, u2 int, u3 int, u7 int, ue int, recDate smalldatetime)
declare @table varchar(50)
set @table = 'testAddAcrossFields'
declare @addFields varchar(500)
set @addFields= ''
select @addFields= '+' +coalesce( cols.name, '') + @addFields from syscolumns cols
inner join sysobjects tables
on cols.id= tables.id
where tables.name= @table and cols.name like 'u%'
order by colid
--print @addFields
declare @sql varchar(500)
set @sql = 'SELECT field1, field2, field3, '
+ @addFields + ' fieldsAdded FROM '
+ @table
/* or maybe need grouping
set @sql = 'SELECT field1, field2, field3,
sum(' + @addFields + ') fieldsAdded FROM '
+ @table + ' group by field1, field2, field3'
*/
--print @sql
exec (@sql)
good luck
Jim J
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply