Column accessing through a column variable

[zhonghandle]

hi,
I am running into a problem that, suppose table A has 10 columns and I want to access one column each time, at the same time keep track of what column that is. For example, I will get all the data from column "c1" and put the summary of the data, say "15" into table B with two columns: one will store "c1", and another one stores the summation ("15&quot. I want to loop the table 10 times (or not necessarily looping the table) so I can just write one query for it. Is there a way?
Or, is there a way to do that in DTS?
Thanks a million in advance.

 

[tlbroadbent]

Does this give you what you seek?

Insert TableB
Select 'C1', Sum(C1) From TableA
Go
Insert TableB
Select 'C2', Sum(C2) From TableA
Go
Insert TableB
Select 'C3', Sum(C3) From TableA
Go
.
.
.
Insert TableB
Select 'C10', Sum(C10) From TableA
Go

You could also use a union query and wouldn't need to insert the result into a table.

Select ColumnName='C1', ColumnSumm=Sum(C1) From TableA
Union All
Insert TableB
Select 'C2', Sum(C2) From TableA
Union All
Insert TableB
Select 'C3', Sum(C3) From TableA
Union All
.
.
.
Union All
Insert TableB
Select 'C10', Sum(C10) From TableA Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.