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