cross tab query on unknown number of rows

[johnv20]

Hi,
I have a table which is dynamically generated every day of the form

Data Value1 Value2 Value3 ..... Value10
ABC 1 2 3 2
ADB 0 2 1 7
etc.

The aim is to get the std dev for each of the data values e.g. ABC

While the table schema will always remain the same I have 2 problems in trying to create a cross tab query to run on this

1: The number of rows in the table is unknown & changes every day

2: The values in the data column changes every day e.g. value ABC may or may not be present on a daily basis

Does anyone have any idea how I could setup a cross tab to do calculations on this ?

 

[LScharf]

Have you looked into using a cursor to loop through the rows and operate on each row one at a time?

 

[johnv20]

Yes - that's what I've had to do - iterate through each row & place in a seperate table to allow for standard dev etc. to be calculated. It just looks very messy & I was hoping there was another way to do it.

Thanks

 

[tc3596]

would it be easier to change the way data is entered into this table, i.e.

Data Value
ABC 1
ABC 2
ABC 3
ADB 2
ADB 3

This way it wouldn't matter how many records got entered, you also may be able to remove the cursor.

Select Data, StDev(Value)
From table