查询中使用列小计

drop   table   table1  
  go  
  create   table   TABLE1(memname   varchar(10),   PROJECT   varchar(10),manhour   int)  
  go  
  insert   into   table1   select   '人员1','工程1',23  
  union   all   select   '人1','工程2',10  
  union   all   select   '人2','工程1',10  
  union   all   select   '人3','工程2',15  
  union   all   select   '人4','工程3',50  
   
   
  declare   @sql   varchar(8000)  
   
  select   @sql   =   '   select   memname'  
  select   @sql   =   @sql   +   ',sum(case   PROJECT   when   '''+PROJECT+'''   then   manhour   else   0   end)   ['+PROJECT+']'  
    from   (select   distinct     PROJECT   from   table1   )   as   a    
  select   @sql   =   @sql+',sum(manhour)   as   合计   from   table1   group   by   memname'  
   
  exec(@sql)  

 

我的使际工作SQL：

 

DECLARE @sql VARCHAR(8000)
          SET @sql = 'select   PROVINCE as 省份,'
          SELECT    @sql = @sql + 'sum(case   CUST_TYPE   when   '''
                    + CUST_TYPE + '''        then  1  
else   0   end)   as   ''' + CUST_TYPE + ''','
          FROM      ( SELECT   DISTINCT
                                CUST_TYPE
                      FROM      ( SELECT    YGCSM_SYS_ENUM_ITEM.ITEM_TITLE AS CUST_TYPE
                                  FROM      YGCSM_SYS_ENUM_ITEM
                                  WHERE     ITEM_TYPE = 2
                                ) AS a
                      WHERE     CUST_TYPE IS NOT NULL
                    ) AS SelectPROVINCE
          SELECT    @sql = LEFT(@sql, LEN(@sql) - 1)
                    + ' ,sum(SUmss) as [小计]  from    (SELECT   
PROVINCE = ( SELECT    YGCSM_SYS_ENUM_ITEM_YGSL.ITEM_TITLE        FROM      YGCSM_SYS_ENUM_ITEM_YGSL        WHERE     ITEM_ID = TempCU.PROVINCE        ),        CUST_TYPE = ( SELECT 
YGCSM_SYS_ENUM_ITEM.ITEM_TITLE        FROM    YGCSM_SYS_ENUM_ITEM        WHERE   ITEM_ID = TempCU.CUST_LEVEL        ), SUmss=1                 from          ((SELECT        DISTINCT       
Customer.CUST_NO,        Customer.CUST_NAME,        Customer.PROVINCE,        Customer.CUST_TYPE1,        Customer.CUST_TYPE2,          Customer.CUST_LEVEL            FROM
YGCSM_SYS_CUSTOMER_CURR Customer          INNER JOIN        dbo.YGCSM_SYS_CUST_TO_ORG_CURR B ON Customer.CUST_NO=B.CUST_NO INNER JOIN        (SELECT EP_QX_USER_TO_ORG.USERID,    
EP_QX_USER_TO_ORG.ORGID       FROM EP_QX_USER_TO_ORG      WHERE  EP_QX_USER_TO_ORG.USERID = 610) as DeptSql ON B.ORG_ID=DeptSql.OrgId          AND Customer.AUDIT_STATE=1 AND
Customer.is_Stop=0))   as   TempCU) AS TempP   group   by   PROVINCE  '
        PRINT @sql
       
       
          EXEC ( @sql
              )

 

 

SQL：

SELECT PROVINCE AS 省份,
                SUM(CASE CUST_TYPE
                      WHEN '多经集团' THEN 1
                      ELSE 0
                    END) AS '多经集团',
                SUM(CASE CUST_TYPE
                      WHEN '多经企业' THEN 1
                      ELSE 0
                    END) AS '多经企业',
                SUM(CASE CUST_TYPE
                      WHEN '发电厂' THEN 1
                      ELSE 0
                    END) AS '发电厂',
                SUM(CASE CUST_TYPE
                      WHEN '附属单位' THEN 1
                      ELSE 0
                    END) AS '附属单位',
                SUM(CASE CUST_TYPE
                      WHEN '集团总部' THEN 1
                      ELSE 0
                    END) AS '集团总部',
                SUM(CASE CUST_TYPE
                      WHEN '区域公司' THEN 1
                      ELSE 0
                    END) AS '区域公司',
                SUM(CASE CUST_TYPE
                      WHEN '省公司' THEN 1
                      ELSE 0
                    END) AS '省公司',
                SUM(CASE CUST_TYPE
                      WHEN '市公司' THEN 1
                      ELSE 0
                    END) AS '市公司',
                SUM(CASE CUST_TYPE
                      WHEN '县公司' THEN 1
                      ELSE 0
                    END) AS '县公司',
                SUM(CASE CUST_TYPE
                      WHEN '乡镇所' THEN 1
                      ELSE 0
                    END) AS '乡镇所',
                SUM(CASE CUST_TYPE
                      WHEN '行业外企业' THEN 1
                      ELSE 0
                    END) AS '行业外企业'
                   ,
                   SUM (SUmss) AS '小计'
         FROM   ( SELECT    PROVINCE = ( SELECT YGCSM_SYS_ENUM_ITEM_YGSL.ITEM_TITLE
                                         FROM   YGCSM_SYS_ENUM_ITEM_YGSL
                                         WHERE  ITEM_ID = TempCU.PROVINCE
                                       ),
                            CUST_TYPE = ( SELECT    YGCSM_SYS_ENUM_ITEM.ITEM_TITLE
                                          FROM      YGCSM_SYS_ENUM_ITEM
                                          WHERE     ITEM_ID = TempCU.CUST_LEVEL
                                        ),
                              SUmss=1          
                  FROM      ( ( SELECT        DISTINCT
                                        Customer.CUST_NO,
                                        Customer.CUST_NAME,
                                        Customer.PROVINCE,
                                        Customer.CUST_TYPE1,
                                        Customer.CUST_TYPE2,
                                        Customer.CUST_LEVEL
                                FROM    YGCSM_SYS_CUSTOMER_CURR Customer
                                        INNER JOIN dbo.YGCSM_SYS_CUST_TO_ORG_CURR B ON Customer.CUST_NO = B.CUST_NO
                                        INNER JOIN ( SELECT EP_QX_USER_TO_ORG.USERID,
                                                            EP_QX_USER_TO_ORG.ORGID
                                                     FROM   EP_QX_USER_TO_ORG
                                                     WHERE  EP_QX_USER_TO_ORG.USERID = 610
                                                   ) AS DeptSql ON B.ORG_ID = DeptSql.OrgId
                                                                   AND Customer.AUDIT_STATE = 1
                                                                   AND Customer.is_Stop = 0
                              )
                            )   as   TempCU
                ) AS TempP
         GROUP   BY PROVINCE