一个使用pivot的存储过程

ALTER PROCEDURE [dbo].[P_GetGameConsumeByType] 
	-- Add the parameters for the stored procedure here
	@dtBegin datetime,
	@dtEnd datetime,
	@nGameType int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    WITH tbl AS(
        SELECT CASE 
            WHEN GoldCount <= 40000 THEN '40000'
            WHEN GoldCount BETWEEN 40001 AND 50000 THEN '50000'
            WHEN GoldCount BETWEEN 50001 AND 60000 THEN '60000'
            WHEN GoldCount BETWEEN 60001 AND 70000 THEN '70000'
            WHEN GoldCount BETWEEN 70001 AND 80000 THEN '80000'
            WHEN GoldCount BETWEEN 80001 AND 90000 THEN '90000'
            WHEN GoldCount BETWEEN 90001 AND 100000 THEN '100000' 
            WHEN GoldCount BETWEEN 100001 AND 110000 THEN '110000'
            ELSE '110000+' END ApproximateGold,
            CASE 
            WHEN MoneyCount <= 200 THEN '200'
            WHEN MoneyCount BETWEEN 201 AND 400 THEN '400'
            WHEN MoneyCount BETWEEN 401 AND 600 THEN '600'
            WHEN MoneyCount BETWEEN 601 AND 800 THEN '800'
            WHEN MoneyCount BETWEEN 801 AND 1000 THEN '1000'
            WHEN MoneyCount BETWEEN 1001 AND 3000 THEN '3000'
            WHEN MoneyCount BETWEEN 3001 AND 5000 THEN '5000'
            WHEN MoneyCount BETWEEN 5001 AND 7000 THEN '7000'
            ELSE '7000+' END ApproximateMoney
            from T_GameMoneyConsume with(nolock) where CreateTime between @dtBegin and @dtEnd and GameType = @nGameType
    ),
    tblStat1 AS
    (
        SELECT ApproximateGold, ApproximateMoney, COUNT(1) AS UserCount FROM tbl GROUP BY ApproximateGold,ApproximateMoney
    )
    
    SELECT 200 'XX数', * FROM (SELECT ApproximateGold, UserCount FROM tblStat1 WHERE [ApproximateMoney] = '200') s
	PIVOT(SUM(UserCount) FOR [ApproximateGold] IN([40000],[50000],[60000],[70000],[80000],[90000],[100000],[110000],[110000+])) A 
	UNION ALL
	SELECT 400 'XX数', * FROM (SELECT ApproximateGold, UserCount FROM tblStat1 WHERE [ApproximateMoney] = '400') s
	PIVOT(SUM(UserCount) FOR [ApproximateGold] IN([40000],[50000],[60000],[70000],[80000],[90000],[100000],[110000],[110000+])) A 
	UNION ALL
	SELECT 600 'XX数', * FROM (SELECT ApproximateGold, UserCount FROM tblStat1 WHERE [ApproximateMoney] = '600') s
	PIVOT(SUM(UserCount) FOR [ApproximateGold] IN([40000],[50000],[60000],[70000],[80000],[90000],[100000],[110000],[110000+])) A 
	UNION ALL
	SELECT 800 'XX数', * FROM (SELECT ApproximateGold, UserCount FROM tblStat1 WHERE [ApproximateMoney] = '800') s
	PIVOT(SUM(UserCount) FOR [ApproximateGold] IN([40000],[50000],[60000],[70000],[80000],[90000],[100000],[110000],[110000+])) A 
	UNION ALL
	SELECT 1000 'XX数', * FROM (SELECT ApproximateGold, UserCount FROM tblStat1 WHERE [ApproximateMoney] = '1000') s
	PIVOT(SUM(UserCount) FOR [ApproximateGold] IN([40000],[50000],[60000],[70000],[80000],[90000],[100000],[110000],[110000+])) A 
	UNION ALL
	SELECT 3000 'XX数', * FROM (SELECT ApproximateGold, UserCount FROM tblStat1 WHERE [ApproximateMoney] = '3000') s
	PIVOT(SUM(UserCount) FOR [ApproximateGold] IN([40000],[50000],[60000],[70000],[80000],[90000],[100000],[110000],[110000+])) A 
	UNION ALL
	SELECT 5000 'XX数', * FROM (SELECT ApproximateGold, UserCount FROM tblStat1 WHERE [ApproximateMoney] = '5000') s
	PIVOT(SUM(UserCount) FOR [ApproximateGold] IN([40000],[50000],[60000],[70000],[80000],[90000],[100000],[110000],[110000+])) A 
	UNION ALL
	SELECT 7000 'XX数', * FROM (SELECT ApproximateGold, UserCount FROM tblStat1 WHERE [ApproximateMoney] = '7000') s
	PIVOT(SUM(UserCount) FOR [ApproximateGold] IN([40000],[50000],[60000],[70000],[80000],[90000],[100000],[110000],[110000+])) A 
	UNION ALL
	SELECT 8000 'XX数', * FROM (SELECT ApproximateGold, UserCount FROM tblStat1 WHERE [ApproximateMoney] = '7000+') s
	PIVOT(SUM(UserCount) FOR [ApproximateGold] IN([40000],[50000],[60000],[70000],[80000],[90000],[100000],[110000],[110000+])) A 
END

最终的到的是一个二维表，第一列列标题是 XX数, 后面的列标题依次是 40000~110000+，可以自己针对这个存储过程建个表填入数据执行看下结果