急求大神帮忙解决方案

急求大神帮忙
一张表里有菜名、门店、是否凉菜，是否海鲜，是否川菜这5个字段，要统计的表字段是：门店，种类（凉菜、海鲜、川菜可以并存），数量，总数，百分比，运用存储过程写，求大神帮忙
------解决思路----------------------

WITH

/* 测试数据

table1(DISH_NAME,STORE_NAME,COLD_DISH_YN,SEAFOOD_YN,SICHUAN_CUISINE_YN)AS(

    SELECT '001','A',1,1,1 UNION ALL

    SELECT '002','B',1,1,0 UNION ALL

    SELECT '003','C',1,0,1 UNION ALL

    SELECT '004','A',1,0,0 UNION ALL

    SELECT '005','B',0,1,1 UNION ALL

    SELECT '006','C',0,1,0 UNION ALL

    SELECT '007','A',0,0,1 UNION ALL

    SELECT '008','B',1,0,0

), */

a AS (

    SELECT STORE_NAME,

           SUM(COLD_DISH_YN) 冷菜,

           SUM(SEAFOOD_YN) 海鲜,

           SUM(SICHUAN_CUISINE_YN) 川菜,

           COUNT(*) TOTAL

      FROM table1

  GROUP BY STORE_NAME

)

,b AS (

    SELECT *

      FROM a

   UNPIVOT (AMOUNT

            FOR KIND IN (冷菜,海鲜,川菜)

           ) u

)

SELECT STORE_NAME,

       CONVERT(nvarchar(2),KIND) KIND,

       AMOUNT,

       TOTAL,

       CONVERT(varchar(6),

               CONVERT(decimal(5,2),AMOUNT*100.0/TOTAL)

              )+'%' BFB

  FROM b

STORE_NAME KIND      AMOUNT       TOTAL BFB

---------- ---- ----------- ----------- -------

A          冷菜           2           3 66.67%

A          海鲜           1           3 33.33%

A          川菜           2           3 66.67%

B          冷菜           2           3 66.67%

B          海鲜           2           3 66.67%

B          川菜           1           3 33.33%

C          冷菜           1           2 50.00%

C          海鲜           1           2 50.00%

C          川菜           1           2 50.00%