SQL Server CE 4.0 - Counting frequency of distinct values in multiple columns

Question

I have a table with 20 columns and each row can have a value of 0-4 (integer) in each column.

I would like to count the number of 0's, the number of 1's, the number of 2's etc in each column for a given subset of the table which is specified in the where clause.

A friend suggested PARTITION, but that's not supported in SQL Server CE. I suspect that I have to use count(expression) but I can't work out what the expression would be.

Can anyone give me a suggestion?

Thanks, Will.

Answer 1

consider you have a table as below

  Select min(subset.g), count(subset.val)
        from
        (
            Select 'c1 & '+cast(Val1 as varchar(4)) as G,
                            val1 as val
                from test
            Union all
                Select 'c2 & '+ cast(Val2 as varchar(4)) as G,
                       val2 as val
                from test   
            Union all
                Select 'c3 & '+ cast(Val3 as varchar(4)) as G,   
                   val3 as val
                from test   
        ) as subset
    Group by  Left(subset.g,2),subset.val
    Order by Left(subset.g,2)

the the result set would be like this