try:
exec('
SELECT customercode, Cost, name, SUM(CASE WHEN state <> '''' THEN 1 ELSE 0 END)
AS issue, SUM(CASE WHEN state = '''' THEN 1 ELSE 0 END) AS before,
SUM(CASE WHEN state <> '''' THEN 1 ELSE 0 END) AS total,
SUM(CASE WHEN state =''active'' THEN 1 ELSE 0 END) AS active,
SUM(CASE WHEN state =''cancelled'' THEN 1 ELSE 0 END) AS cancelled,
SUM(CASE WHEN state =''new'' THEN 1 ELSE 0 END) AS new,
SUM(CASE WHEN state = ''completed'' THEN 1 ELSE 0 END) AS completed,
SUM(CASE WHEN state = ''held'' THEN 1 ELSE 0 END) AS held,
SUM(CASE WHEN state =''obsolete'' THEN 1 ELSE 0 END) AS obsolete
FROM pmshe
GROUP BY customercode, Cost, name
'
)
exec('
SELECT customercode, Cost, name, SUM(CASE WHEN state <> '''' THEN 1 ELSE 0 END)
AS issue, SUM(CASE WHEN state = '''' THEN 1 ELSE 0 END) AS before,
SUM(CASE WHEN state <> '''' THEN 1 ELSE 0 END) AS total,
SUM(CASE WHEN state =''active'' THEN 1 ELSE 0 END) AS active,
SUM(CASE WHEN state =''cancelled'' THEN 1 ELSE 0 END) AS cancelled,
SUM(CASE WHEN state =''new'' THEN 1 ELSE 0 END) AS new,
SUM(CASE WHEN state = ''completed'' THEN 1 ELSE 0 END) AS completed,
SUM(CASE WHEN state = ''held'' THEN 1 ELSE 0 END) AS held,
SUM(CASE WHEN state =''obsolete'' THEN 1 ELSE 0 END) AS obsolete
FROM pmshe
GROUP BY customercode, Cost, name
'
)
在 state <> '' 这个地方
所以每一行都要连接的。