SELECT 

    
    ISNULL(SUM(CASE [box] WHEN '红盒子中' THEN [Sum] END), 0) AS [红盒子], 

    ISNULL(SUM(CASE [box] WHEN '黄盒子中' THEN [Sum] END), 0) AS [黄盒子], 

    ISNULL(SUM(CASE [box] WHEN '蓝盒子中' THEN [Sum] END), 0) AS [绿盒子] 

FROM 

( 
    SELECT 

        [box], 

        sum(number) AS [Sum] 

    FROM 

        [test1] 

    GROUP BY 

        [box] 

)AS [Temp]

------解决方案--------------------
symbol441

如果盒子是不确定的！
怎可以那样。

就按盒子分组，计算总数！
然后绑定！这样才比较合理的
sql = "SELECT 盒子,SUM(数量)  FROM TABLE BROUP BY 盒子"  

        <asp:Repeater id="repList" runat="server" >  
        <HeaderTemplate >  
        <table >  
        <tr >  
        </HeaderTemplate >  
        <ItemTemplate >  
            <td >  
                <table  >  
                    <tr > <td >绑盒子 </td > </tr >  
                    <tr > <td >绑数量 </td > </tr >  
                </table >  
            </td >  
        </ItemTemplate >  
        <FooterTemplate >  
        </tr >  
        </table >  
        </FooterTemplate >  
        </asp:Repeater >