SQL query to return columns per value in value range? I have a table like this:


Date                 Value1         Value2
2009-12-30           23334          93
2009-12-31           21989          97
2010-01-01           18638          62
2010-01-02           19666          138
2010-01-03           19527          112
2010-01-04           23111          135
2010-01-05           23893          135
2010-01-06           23912          131
2010-01-07           23587          111
2010-01-08           23224          151
.                    .              . 
.                    .              .
.                    .              .

and i need to convert this table to two dimensional table. So Value1 ranges go to right and Value2 ranges go to bottom like this:


             [0-20000] [20000-21000] [21000-22000] [22000-23000] [23000-24000] ...Value1
[0-70]          1            0             0             0             0
[70-90]         0            0             0             0             0
[90-110]        0            0             1             0             1 
[110-130]       1            0             0             0             1
[130-150]       1            0             0             0             3
[150-170]       0            0             0             0             1
.
.
.
Value2




Please help me
I used the basis of a pivot table query on StackOverflow. Here is a similar post to the one that I used in my query below. Here is a more general link for pivot tables.

SELECT [Range]
       ,[0-20000]=ISNULL(SUM([0-20000]),0)
       ,[20001-21000]=ISNULL(SUM([20001-21000]),0)
       ,[21001-22000]=ISNULL(SUM([21001-22000]),0)
       ,[22001-23000]=ISNULL(SUM([22001-23000]),0)
       ,[23001-24000]=ISNULL(SUM([23001-24000]),0)
FROM (
    SELECT  CASE     WHEN Value2 BETWEEN 0 AND 70 THEN 'a 0-70'
                     WHEN Value2 BETWEEN 71 AND 90 THEN 'b 71-90'
                     WHEN Value2 BETWEEN 91 AND 110 THEN 'c 91-110'
                     WHEN Value2 BETWEEN 111 AND 130 THEN 'd 111-130'
                     WHEN Value2 BETWEEN 131 AND 150 THEN 'e 131-150'
                     WHEN Value2 BETWEEN 151 AND 170 THEN 'f 151-170'
                     ELSE 'Over 170' END AS Range,
                    *
     FROM 
        (SELECT     value2,
                    Range2 AS [Value],
                    COUNT(*)  AS Foo
        FROM    (SELECT 
        value2,
        CASE WHEN Value1 BETWEEN 0 AND 20000 THEN '0-20000'
                     WHEN Value1 BETWEEN 20001 AND 21000 THEN '20001-21000'
                     WHEN Value1 BETWEEN 21001 AND 22000 THEN '21001-22000'
                     WHEN Value1 BETWEEN 22001 AND 23000 THEN '22001-23000'
                     WHEN Value1 BETWEEN 23001 AND 24000 THEN '23001-24000'
                     ELSE 'Over 24000' END AS Range2
                FROM    (SELECT value1,value2
                         FROM StackDBA) AS derivedtbl_1) AS t
        GROUP BY Range2, value2) q1
        pivot
        (max(foo)
         for [value] in ([0-20000],[20001-21000],[21001-22000],[22001-23000],[23001-       24000])
        )as pvt
) grp

GROUP BY [Range]


SQL Fiddle