Consulta com PIVOT

Produtos da Linha Protheus Sigafat - Faturamento - Backoffice
,
1

Alguém tem algum exemplo de query com pivot dinâmico?

1 curtida
2

Estou tentando fazer para deixar automático.
SELECT PRODUTO, [1], [2], [3]
FROM (
SELECT D1_COD AS PRODUTO, MONTH(D1_EMISSAO) AS MES, SUM(D1_TOTAL) D1_TOTAL
FROM SD1120 WHERE MONTH(D1_EMISSAO) BETWEEN ‘1’ AND ‘3’
GROUP BY D1_COD, MONTH(D1_EMISSAO)
) AS ORIGEM
PIVOT (
SUM(D1_TOTAL) FOR MES IN ([1], [2], [3])
) AS PivotTable
ORDER BY PRODUTO;

1 curtida
3

@dilsoncastro , tente algo assim:

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);

SELECT @cols = STRING_AGG(QUOTENAME(MONTH(D1_EMISSAO)), ',')
FROM (SELECT DISTINCT MONTH(D1_EMISSAO) AS MES FROM SD1120 WHERE MONTH(D1_EMISSAO) BETWEEN 1 AND 3) AS Temp;

SET @sql = '
SELECT PRODUTO, ' + @cols + '
FROM (
    SELECT D1_COD AS PRODUTO, MONTH(D1_EMISSAO) AS MES, SUM(D1_TOTAL) D1_TOTAL
    FROM SD1120
    WHERE MONTH(D1_EMISSAO) BETWEEN 1 AND 3
    GROUP BY D1_COD, MONTH(D1_EMISSAO)
) AS ORIGEM
PIVOT (
    SUM(D1_TOTAL) FOR MES IN (' + @cols + ')
) AS PivotTable
ORDER BY PRODUTO;';

EXEC sp_executesql @sql;
2 curtidas
4

Gerou erro então alterei:
DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);

– Garante que não há duplicatas nos meses extraídos
SELECT @cols = STRING_AGG(QUOTENAME(MES), ‘,’)
FROM (
SELECT DISTINCT MONTH(D1_EMISSAO) AS MES
FROM SD1120
WHERE MONTH(D1_EMISSAO) BETWEEN 1 AND 3
) AS Temp;

– Evita erro caso @cols esteja vazio
IF @cols IS NULL OR @cols = ‘’
BEGIN
PRINT ‘Nenhum dado encontrado para o período especificado.’;
RETURN;
END

SET @sql = ’
SELECT PRODUTO, ’ + @cols + ’
FROM (
SELECT D1_COD AS PRODUTO, MONTH(D1_EMISSAO) AS MES, SUM(D1_TOTAL) AS D1_TOTAL
FROM SD1120
WHERE MONTH(D1_EMISSAO) BETWEEN 1 AND 3
GROUP BY D1_COD, MONTH(D1_EMISSAO)
) AS ORIGEM
PIVOT (
SUM(D1_TOTAL) FOR MES IN (’ + @cols + ‘)
) AS PivotTable
ORDER BY PRODUTO;’;

– Debugging: Ver SQL gerado
PRINT @sql;

EXEC sp_executesql @sql;

5

Será que tem alguma forma de ordenar as colunas?

6

Para ordenar os meses, tente isso:

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);

-- Garante que não há duplicatas nos meses extraídos e garante que os meses estejam ordenados corretamente
SELECT @cols = STRING_AGG(QUOTENAME(MES), ',') 
FROM (
    SELECT DISTINCT MONTH(D1_EMISSAO) AS MES 
    FROM SD1120 
    WHERE MONTH(D1_EMISSAO) BETWEEN 1 AND 3
    ORDER BY MES -- Garante a ordem correta dos meses
) AS Temp;

-- Evita erro caso @cols esteja vazio
IF @cols IS NULL OR @cols = ''
BEGIN
    PRINT 'Nenhum dado encontrado para o período especificado.';
    RETURN;
END

-- Monta a query dinâmica
SET @sql = '
SELECT PRODUTO, ' + @cols + '
FROM (
    SELECT D1_COD AS PRODUTO, MONTH(D1_EMISSAO) AS MES, SUM(D1_TOTAL) AS D1_TOTAL
    FROM SD1120
    WHERE MONTH(D1_EMISSAO) BETWEEN 1 AND 3
    GROUP BY D1_COD, MONTH(D1_EMISSAO)
) AS ORIGEM
PIVOT (
    SUM(D1_TOTAL) FOR MES IN (' + @cols + ')
) AS PivotTable
ORDER BY PRODUTO;';

-- Debugging: Ver SQL gerado
PRINT @sql;

-- Executar a query dinâmica
EXEC sp_executesql @sql;
7

@dilsoncastro , deu certo a ordenação?

8

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

9

Veja agora:

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);

-- Garante que não há duplicatas nos meses extraídos e mantém a ordem correta
SELECT @cols = STRING_AGG(QUOTENAME(MES), ',') WITHIN GROUP (ORDER BY MES)
FROM (
    SELECT DISTINCT MONTH(D1_EMISSAO) AS MES 
    FROM SD1120 
    WHERE MONTH(D1_EMISSAO) BETWEEN 1 AND 3
) AS Temp;

-- Evita erro caso @cols esteja vazio
IF @cols IS NULL OR @cols = ''
BEGIN
    PRINT 'Nenhum dado encontrado para o período especificado.';
    RETURN;
END

-- Monta a query dinâmica
SET @sql = '
SELECT PRODUTO, ' + @cols + '
FROM (
    SELECT D1_COD AS PRODUTO, MONTH(D1_EMISSAO) AS MES, SUM(D1_TOTAL) AS D1_TOTAL
    FROM SD1120
    WHERE MONTH(D1_EMISSAO) BETWEEN 1 AND 3
    GROUP BY D1_COD, MONTH(D1_EMISSAO)
) AS ORIGEM
PIVOT (
    SUM(D1_TOTAL) FOR MES IN (' + @cols + ')
) AS PivotTable
ORDER BY PRODUTO;';

-- Debugging: Ver SQL gerado
PRINT @sql;

-- Executar a query dinâmica
EXEC sp_executesql @sql;