G
Garry
Hello everyone.
I have to process a large table with column names that are not fixed.
Each record contains digits. Some columns will have digits from 1 to
5, some 1 to 6, some 3 to 5 etc. I want to count the number of each
type of digit in each column eg.
A)
Digit Col1 Col2 Col3 ... Col100
1 10 12 0 0
2 5 7 9 5
3 6 8 12 ... 7
4 10 6 1 ... 0
5
etc.
or B)
Name Digit CountofDigit
Col1 1 10
Col1 2 5
Col1 3 6
Col1 4 10
Col2 1 12
Col2 2 7
etc.
Can anyone suggest how to write a query to do this? I can produce
output like B) by programatically writing a separate query for each
column and joining them with a UNION statement. But there may be up to
100 columns, and 50,000 records, and this method is too slow (as well
as being ugly).
Any ideas gratefully received!
Regards
Garry
I have to process a large table with column names that are not fixed.
Each record contains digits. Some columns will have digits from 1 to
5, some 1 to 6, some 3 to 5 etc. I want to count the number of each
type of digit in each column eg.
A)
Digit Col1 Col2 Col3 ... Col100
1 10 12 0 0
2 5 7 9 5
3 6 8 12 ... 7
4 10 6 1 ... 0
5
etc.
or B)
Name Digit CountofDigit
Col1 1 10
Col1 2 5
Col1 3 6
Col1 4 10
Col2 1 12
Col2 2 7
etc.
Can anyone suggest how to write a query to do this? I can produce
output like B) by programatically writing a separate query for each
column and joining them with a UNION statement. But there may be up to
100 columns, and 50,000 records, and this method is too slow (as well
as being ugly).
Any ideas gratefully received!
Regards
Garry