Início / dba / Perguntas / 208818
Accepted
Mark D
Asked: 2018-06-06 08:54:44 +0800 CST

Pivot com rollup no sql server

  • 772

É possível usar um cursor para criar uma tabela dinâmica e depois usar essas colunas para agregar dados no SQL Server 2008?

Por exemplo, pegue a tabela a seguir.

CREATE TABLE Billing (
    BillingId BIGINT IDENTITY,
    SubscriptionId BIGINT,
        ExternalServiceName VARCHAR(50),
        BillYear INT NOT NULL,
    BillMonth INT NOT NULL
);

INSERT INTO Billing (BillingId, SubscriptionId, ExternalServiceName,
                     BillYear, BillMonth)
VALUES (1, 1, 'Dogs', 2018, 4),
       (2, 2, 'Cats', 2018, 4),
       (3, 1, 'Dogs', 2018, 5),
       (4, 2, 'Cats', 2018, 5);

CREATE TABLE BillingData (
    BillingDataId INT IDENTITY PRIMARY KEY,
    BillingId INT NOT NULL,
    Feature VARCHAR(50) NOT NULL,
    Usage INT NOT NULL,
    Measurement VARCHAR(50),
    Cost NUMERIC(18,2) NOT NULL
);

INSERT INTO BillingData(BillingId, Feature, Usage, Measurement, Cost)
VALUES (1, 'Walks', 25, 'walks', 200.32),
       (1, 'Baths', 5, 'baths', 251.32),
       (2, 'Litter change', 53, 'changes', 110.21),
       (2, 'Groom', 25, 'brushings', 123),
       (2, 'Scratching', 213, 'clipping', 123),
       (3, 'Pilling', 11, 'medicate', 10),
       (4, 'Groom', 5, 'brushings', 50),
       (4, 'Exercise', 1, 'run', 25.12),
       (1, 'Walks', 500, 'walks', 12351.31),
       (1, 'Baths', 53, 'baths', 1235),
       (2, 'Baths', 53, 'baths', 1235);

O que eu gostaria de poder fazer é criar uma tabela com este formato

 +-------------+---------+---------+-----------------+---------+--------------+---------+----------+
 | [BillingId] | [Walks] | [Baths] | [Litter change] | [Groom] | [Scratching] | [Usage] | [Cost]   |
 +-------------+---------+---------+-----------------+---------+--------------+---------+----------+
 | 1           | 525     | 58      | 0               | 0       | 0            | 583     | 14037.95 |
 | 2           | 0       | 53      | 53              | 25      | 213          | 344     | 1591.21  |
 +-------------+---------+---------+-----------------+---------+--------------+---------+----------+

A única maneira que consegui pensar para fazer isso foi agregar a tabela vertical.

Fazendo algo como a seguinte consulta

SELECT MAX(BillingId), MAX(Feature), SUM(Usage), MAX(Measurement), SUM(Cost) 
FROM BillingData;

Mas então eu teria que juntar dinamicamente essas colunas na tabela Billing, especialmente porque o BillingData pode não ser o mesmo mês a mês. Por exemplo:

SELECT DISTINCT Feature FROM BillingData WHERE BillYear=2018 AND BillMonth=5;

É diferente de

SELECT DISTINCT Feature FROM BillingData WHERE BillYear=2018 and BillMonth=4;

Assim, enquanto as colunas BillingId, Walks, Baths, Litter change, Groom, Scratching, Usage, Cost são apropriadas para abril, as colunas de maio seriam apenas BillingId, Pilling, Groom, Exercise, Usage e Cost.

Acredito que uma tabela dinâmica pode ser o que eu preciso aqui, mas suspeito que ela precise ser dinâmica, pois as colunas precisariam ser diferentes para cada mês.

Não tenho certeza da melhor maneira de fazer isso. Alguma ajuda seria muito apreciada.

sql-server sql-server-2008

1 respostas

  1. Best Answer

    Isso pode ser feito PIVOTe pode ser feito dinamicamente, mas antes de tentar fazer isso dinamicamente, você deve tentar obter o resultado desejado usando uma versão estática ou codificada da consulta e convertê-la em sql dinâmico .

    Como você está usando o SQL Server 2008 e deseja uma coluna total para Usagee Cost, eu começaria primeiro examinando sum(<your column) over(...). Isso permitirá que você agregue seus dados em uma única etapa antes de dinamizá-los.

    Para obter a versão estática, eu começaria primeiro com uma consulta semelhante a esta:

    select 
    b.BillingId,
    bd.Feature,
    bd.Usage,
    TotalUsage = sum(bd.Usage) over(partition by bd.BillingId),
    TotalCost = sum(bd.Cost) over(partition by bd.BillingId)
from Billing b
inner join BillingData bd
    on b.BillingId = bd.BillingId
where b.BillYear = 2018 and b.BillMonth = 4

    Consulte SQL Fiddle . Essa consulta fornece os dados básicos que você deseja dinamizar:

    | BillingId |       Feature | Usage | TotalUsage | TotalCost |
|-----------|---------------|-------|------------|-----------|
|         1 |         Walks |    25 |        583 |  14037.95 |
|         1 |         Baths |     5 |        583 |  14037.95 |
|         1 |         Walks |   500 |        583 |  14037.95 |
|         1 |         Baths |    53 |        583 |  14037.95 |
|         2 |         Baths |    53 |        344 |   1591.21 |
|         2 | Litter change |    53 |        344 |   1591.21 |
|         2 |         Groom |    25 |        344 |   1591.21 |
|         2 |    Scratching |   213 |        344 |   1591.21 |

    Incluindo seu BillingId, cada um dos Featuresque você eventualmente deseja em uma nova coluna, depois o Usage, TotalUsagee TotalCostpara cada BillingId. O sum(<yourcolumn> over(partition by bd.BillingId)dá-lhe o valor de cada conta sem ter que usar GROUP BY. Depois de obter esses dados, você pode aplicar a PIVOTfunção:

    select 
    BillingId,
    Walks = IsNull(Walks, 0),
    Baths = IsNull(Baths, 0),
    [Litter Change] = IsNull([Litter Change], 0),
    Groom = IsNull(Groom, 0),
    Scratching = IsNull(Scratching, 0),
    Usage = TotalUsage,
    Cost = TotalCost
from
(
    select 
        b.BillingId,
        bd.Feature,
        bd.Usage,
        TotalUsage = sum(bd.Usage) over(partition by bd.BillingId),
        TotalCost = sum(bd.Cost) over(partition by bd.BillingId)
    from Billing b
    inner join BillingData bd
        on b.BillingId = bd.BillingId
    where b.BillYear = 2018 and b.BillMonth = 4
) x
pivot
(
    sum(Usage)
    for Feature in ([Walks], [Baths], [Litter Change], [Groom], [Scratching])
) piv;

    Consulte SQL Fiddle para demonstração . Isso dá um resultado:

    | BillingId | Walks | Baths | Litter Change | Groom | Scratching | Usage |     Cost |
|-----------|-------|-------|---------------|-------|------------|-------|----------|
|         1 |   525 |    58 |             0 |     0 |          0 |   583 | 14037.95 |
|         2 |     0 |    53 |            53 |    25 |        213 |   344 |  1591.21 |

    Agora que você obteve o resultado final que procura, pode começar a converter a consulta em SQL dinâmico. Para fazer isso, você precisará obter uma lista dos valores que deseja que sejam a coluna, também conhecida como Featurevalores. Isso é feito consultando suas tabelas com o BillYeare BillMonthvocê deseja e concatenando os valores em uma string, pegando essa lista de colunas e executando uma string sql completa. O código completo pode ser semelhante a:

    DECLARE 
    @BillYear int = 2018,
    @BillMonth int = 4,
    @colsNull AS NVARCHAR(MAX),
    @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ', ' + QUOTENAME(bd.Feature)
                    from Billing b
                    inner join BillingData bd
                        on b.BillingId = bd.BillingId
                    where b.BillYear = @BillYear 
                        and b.BillMonth = @BillMonth
                    group by bd.Feature
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @colsNull = STUFF((SELECT ', IsNull(' + QUOTENAME(bd.Feature)+',0) as '+ QUOTENAME(bd.Feature)
                    from Billing b
                    inner join BillingData bd
                        on b.BillingId = bd.BillingId
                    where b.BillYear = @BillYear 
                        and b.BillMonth = @BillMonth
                    group by bd.Feature
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

set @query = N'SELECT BillingId, ' + @colsNUll + N', TotalUsage, TotalCost 
            from 
             (
                select 
                    b.BillingId,
                    bd.Feature,
                    bd.Usage,
                    TotalUsage = sum(bd.Usage) over(partition by bd.BillingId),
                    TotalCost = sum(bd.Cost) over(partition by bd.BillingId)
                from Billing b
                inner join BillingData bd
                    on b.BillingId = bd.BillingId
                where b.BillYear = '+cast(@BillYear as nvarchar(4))+N' 
                  and b.BillMonth = '+cast(@BillMonth as nvarchar(2))+N'
            ) x
            pivot 
            (
                sum(Usage)
                for Feature in (' + @cols + N')
            ) p '

exec sp_executesql @query;

    Consulte SQL Fiddle com demonstração . Você notará que existem duas variáveis ​​para as colunas - uma @colsque é usada dentro da PIVOTfunção e, em seguida, @colsNullé semelhante à primeira, mas substitui a nullsna lista de seleção final por zero - você pode excluir usando isso se não não precisa. Se você executar isso para BillingMonth = 4obterá o mesmo resultado que a versão estática:

    | BillingId | Baths | Groom | Litter change | Scratching | Walks | TotalUsage | TotalCost |
|-----------|-------|-------|---------------|------------|-------|------------|-----------|
|         1 |    58 |     0 |             0 |          0 |   525 |        583 |  14037.95 |
|         2 |    53 |    25 |            53 |        213 |     0 |        344 |   1591.21 |

    Então, se você alterar o BillingMonth = 5, obterá os resultados sem precisar alterar a consulta ( Demo ):

    | BillingId | Exercise | Groom | Pilling | TotalUsage | TotalCost |
|-----------|----------|-------|---------|------------|-----------|
|         3 |        0 |     0 |      11 |         11 |        10 |
|         4 |        1 |     5 |       0 |          6 |     75.12 |
    • 7

relate perguntas