how to generate a report in sql server from a previous select

Question

Periodically I need to generate a report based on some data from database.

The first select IS:

SELECT DISTINCT
                 A.PRO_C_NOME,
                 B.ETS_C_NOME        
                FROM WETI_ETAPA_ITEM F   
                 INNER JOIN WETE_ETAPA_ITEM_EDITORS E   
                  ON E.ETE_N_ETI_N_CODIGO = F.ETI_N_CODIGO   
                 INNER JOIN WETA_ETAPA_PROCESSO H   
                  ON H.ETA_N_CODIGO = F.ETI_N_ETA_N_CODIGO   
                 INNER JOIN WIPR_ITEM_PROCESSO C   
                  ON C.IPR_N_CODIGO = F.ETI_N_IPR_N_CODIGO   
                 INNER JOIN WIWF_ITEM_WORKFLOW D  
                  ON D.IWF_N_CODIGO = C.IPR_N_IWF_N_CODIGO  
                 INNER JOIN WPRO_PROCESSO A    
                  ON A.PRO_N_CODIGO = C.IPR_N_PRO_N_CODIGO AND PRO_N_DELETED = 0  
                 LEFT OUTER JOIN WISP_ITEM_SUBPROCESS G    
                  ON G.ISP_N_ID = F.ETI_ISP_N_ID
                 INNER JOIN WETS_ETAPA_SLA B
                  ON F.ETI_N_ETS_N_CODIGO = B.ETS_N_CODIGO

Returns something like this:

C1	C2
A	1
A	1
A	3
B	2
B	2
B	2
B	3
B	3
B	3
C	1
C	2

I need a report from that first select returning something like this:

S.1	S.2	S.3	S.4
A	2	0	1
B	0	3	3
C	1	1	0

that is:

S.1 - distinct values from C1
S.2 - count 1 values in C2 for the S.1 value
S.3 - count 2 values in C2 for the S.1 value
S.4 - count 3 values in C2 for the S.1 value

Can someone help how do I solve this?

I tried many solutions, such as using temporary tables and selecting from another select but doesn't work at all for different reasons.

Answer 1

;with cte(PRO_C_NOME, ETS_C_NOME)  as (
SELECT DISTINCT
                 A.PRO_C_NOME,
                 B.ETS_C_NOME        
                FROM WETI_ETAPA_ITEM F   
                 INNER JOIN WETE_ETAPA_ITEM_EDITORS E   
                  ON E.ETE_N_ETI_N_CODIGO = F.ETI_N_CODIGO   
                 INNER JOIN WETA_ETAPA_PROCESSO H   
                  ON H.ETA_N_CODIGO = F.ETI_N_ETA_N_CODIGO   
                 INNER JOIN WIPR_ITEM_PROCESSO C   
                  ON C.IPR_N_CODIGO = F.ETI_N_IPR_N_CODIGO   
                 INNER JOIN WIWF_ITEM_WORKFLOW D  
                  ON D.IWF_N_CODIGO = C.IPR_N_IWF_N_CODIGO  
                 INNER JOIN WPRO_PROCESSO A    
                  ON A.PRO_N_CODIGO = C.IPR_N_PRO_N_CODIGO AND PRO_N_DELETED = 0  
                 LEFT OUTER JOIN WISP_ITEM_SUBPROCESS G    
                  ON G.ISP_N_ID = F.ETI_ISP_N_ID
                 INNER JOIN WETS_ETAPA_SLA B
                  ON F.ETI_N_ETS_N_CODIGO = B.ETS_N_CODIGO
)

    SELECT PRO_C_NOME AS 'S.1'
       ,SUM(CASE WHEN ETS_C_NOME=1 THEN 1 ELSE 0 END) AS 'S.2'
       ,SUM(CASE WHEN ETS_C_NOME=2 THEN 1 ELSE 0 END) AS 'S.3'
       ,SUM(CASE WHEN ETS_C_NOME=3 THEN 1 ELSE 0 END) AS 'S.4'
    FROM cte
    GROUP BY PRO_C_NOME