O código que tenho abaixo informa os valores em linha separadas, estes valores deveriam estar na mesma linha conforme o QA_grp.
SELECT 'textil' AS DESC_FILIAL,
QA_GRP AS QA_GRP,
'INDUSTRIAL' AS SETOR,
SUM(TOTAL_NAOCONFORME) AS TOTAL_NAOCONFORME,
SUM(PRODUCAO) AS PRODUCAO
FROM
(
SELECT 'textil' AS DESC_FILIAL,
GRP AS QA_GRP,
'INDUSTRIAL' AS SETOR,
0 AS TOTAL_NAOCONFORME,
PRODUCAO AS PRODUCAO
FROM (
SELECT SUM(VPF.QUILOS) as producao,
'ACABAMENTO' as grp
FROM VW_PI_CBPAP02_PRODBENEF VPF
WHERE VPF.FILIAL = 1
AND VPF.PERIODO = 1
AND VPF.GRUPO='ACA06'
AND VPF.DATA_PROD BETWEEN DATA_INI_F AND DATA_FIM_F
union
SELECT SUM(VPF.QUILOS) as producao,
'TINTURARIA' as grp
FROM VW_PI_CBPAP02_PRODBENEF VPF
WHERE VPF.FILIAL = 1
AND VPF.PERIODO = 1
AND VPF.DESCR_FASE LIKE 'TINGIR%'
AND VPF.DATA_PROD BETWEEN DATA_INI_F AND DATA_FIM_F
union
SELECT SUM(VPF.QUILOS) as producao,
'ESTAMPARIA' as grp
FROM VW_PI_CBPAP02_PRODBENEF VPF
WHERE VPF.FILIAL = 1
AND VPF.PERIODO = 1
AND VPF.NUMERO_MAQUINA in ('ME004','ME001')
AND VPF.DATA_PROD BETWEEN DATA_INI_F AND DATA_FIM_F
union
SELECT SUM(CEC.QUANT) as producao,
'MALHARIA' as grp
FROM VW_PI_CBEPC09_EMB_CRU CEC
WHERE CEC.SETOR IN (4, 6, 7, 16)
AND CEC.FILIAL = 1
AND CEC.DATA_PROD BETWEEN DATA_INI_F AND DATA_FIM_F
)
union
SELECT 'textil' AS DESC_FILIAL,
NVL(GRP.DESCRICAO_GRUPO,'não CLASSIFICADO') AS QA_GRP,
'INDUSTRIAL' AS SETOR,
SUM(RE.QUANT) AS TOTAL_NAOCONFORME,
0 AS PRODUCAO
FROM VW_PI_CBEPA02_MOV_ACA RE
LEFT OUTER JOIN GERAPECAREVISAO REV ON (REV.IDPECASPRODUTO=RE.IDPECASPRODUTO)
LEFT OUTER JOIN GRUPO_DEFEITO_QUALID GRP ON (GRP.CODIGO_GRUPO=REV.GRUPO_DEFEITO)
LEFT OUTER JOIN GERAPECADESTINOOB GOB ON (GOB.IDPECASPRODUTO = RE.IDPECASPRODUTO)
LEFT OUTER JOIN OB OB ON (OB.NUMERO_OB = GOB.NUMERO_OB)
WHERE RE.deposito IN (54,55,51)
AND RE.NUM_TIPO_MOVIMENTO IN (37)
AND OB.CODIGO_FLUXO NOT IN (735)
AND RE.QA NOT IN ('1 ')
AND RE.DATA_MOV BETWEEN data_ini_f AND data_fim_f
AND grp.codigo_grupo in ('TC','AT','AR','ES')
GROUP BY NVL(GRP.DESCRICAO_GRUPO,'não CLASSIFICADO')
)
GROUP BY DESC_FILIAL,
QA_GRP,
SETOR
Ele retornar a informação assim:
1 textil ESTAMPARIA INDUSTRIAL 0 178328,66
2 textil ESTAMPARIA INDUSTRIAL 3135,5 0
3 textil TINTURARIA INDUSTRIAL 0 161433,84
4 textil TINTURARIA INDUSTRIAL 2475,72 0
Gostaria que fosse assim:
1 textil ESTAMPARIA INDUSTRIAL 3135,5 178328,66
4 textil TINTURARIA INDUSTRIAL 2475,72 161433,84