Pivot with rollup in sql server

Is it possible to use a cursor to create a dynamic table and then use those columns to aggregate data in SQL server 2008?

For example take the following table.

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);

What I'd like to be able to do is create a table with this format

```
+-------------+---------+---------+-----------------+---------+--------------+---------+----------+
| [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 |
+-------------+---------+---------+-----------------+--------

This can be done with PIVOT and it can be done dynamically, but before you jump in trying to do this dynamically, you should try to get the result you want using a static or hard-coded version of the query, then convert it to dynamic sql.

Since you are using SQL Server 2008 and you want a total column for both Usage and Cost, I would first start by looking at sum( over(partition by bd.BillingId) gives you the value for each account without having to use GROUP BY. Once you've got this data, you can apply the PIVOT function:

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;

See SQL Fiddle for Demo. This gives a result:

| 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 |

Now that you've gotten the final result you are looking for, you can start converting the query to dynamic SQL. In order you do this, you will need to get a list of the values you want to be column aka the Feature values. This is done by querying your tables with the BillYear and BillMonth you want, and concatenating the values into a string, then taking that list of columns and executing a full sql string. The full code could be similar to:

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;

See SQL Fiddle with Demo. You'll notice that there are two variables for the columns - one @cols this is used inside of the PIVOT function and then @colsNull this is similar to the first, but it replaces the nulls in the final select list with zero - you can exclude using this if you don't need it. If you execute this for BillingMonth = 4 you'll get the same result as the static version:

| 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 |

Then if you change the BillingMonth = 5 you get the results without having to change the query (Demo):

```
| BillingId | Exercise | Groom | Pilling | TotalUsage | TotalCost |
|-----------|----------|-------|--------

StackExchange Database Administrators Q#208818, answer score: 7