Pivot with rollup in sql server

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(<your column) over(...). This will allow you to aggregate your data in a single step before pivoting it.

To get the static version, I'd first start out with query similar to this:

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

See SQL Fiddle. This query gets you the basic data that you want to pivot:

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

Including your BillingId, each of the Features you eventually want in a new column, then the Usage, TotalUsage, and TotalCost for each BillingId. The sum(<yourcolumn> 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 |
|-----------|----------|-------|---------|------------|-----------|
|         3 |        0 |     0 |      11 |         11 |        10 |
|         4 |        1 |     5 |       0 |          6 |     75.12 |