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 |
+-------------+---------+---------+-----------------+---------+--------------+---------+----------+
The only way I could think to accomplish this was to aggregate the vertical table.
By doing something like the following query
SELECT MAX(BillingId), MAX(Feature), SUM(Usage), MAX(Measurement), SUM(Cost)
FROM BillingData;
But then I'd have to dynamically join those columns into the Billing table, especially since the BillingData may not be the same from month to month. For example:
SELECT DISTINCT Feature FROM BillingData WHERE BillYear=2018 AND BillMonth=5;
Is different from
SELECT DISTINCT Feature FROM BillingData WHERE BillYear=2018 and BillMonth=4;
So while the columns BillingId, Walks, Baths, Litter change, Groom, Scratching, Usage, Cost are appropriate for April the columns for May would be just BillingId, Pilling, Groom, Exercise, Usage and Cost.
I believe a pivot table may be what I need here but I suspect it may need to be dynamic as columns would need to be different for each month.
I'm not sure the best way to go about doing this. Some help would be greatly appreciated.
1 Answer 1
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 |