Did he want the Monarchy to collapse?
I think you have his motivations inverted: while Camillo Benso the Count of Cavour would have probably been happy to see the Austrian Empire collapse, it doesn't figure among his major motivations. Rather, he seems to have been an avowed Liberal (even more so than an Italian Nationalist) and his political work was wholly dedicated to creating a large, industrialized, and modern state in Italy; he considered anything that got in the way of that objective an obstacle, and that is how he came to shape his aggressive policy stance against Austria. But while he understood that a necessary condition to reach his social and ideological objectives would mean weakening the various permutations of the Austrian Empire on the Italian peninsula (if possible expelling Austrian presence from Italy altogether) he also never really acted to accelerate the Empire’s decline outside of moving opportunistically within the larger games of the European Balance of Power. Cavour was a pragmatist, and ultimately acted in order to preserve the Kingdom of Piedmont-Sardinia and its political order’s leadership in Italian affairs. This was not a radical position: by the time he became Prime Minister of the Kingdom of Sardinia-Piedmont, there existed a loud current in parliament, in the press, and in wider society which strongly agitated for immediate action against the Austrian Empire with the goal of unifying the Italian peninsula, and indeed unification would come to be common objective to which a multitude of social currents converged and agreed on. With this in mind, compared to many of his peers in the pantheon of Italy's founding fathers, the Count of Cavour was actually fairly cautious and moderate, if not objectively conservative.
When making his decisions and taking action, the Count of Cavour’s opinions were shaped, rather predictably, by his upbringing, by his personal experiences, and his self-interest, but also the various social and political currents which he saw emerge around him and to which he was continuously reacting to (this is, after all, the principal concern of any country's Prime Minister). He did not immediately have a clear vision of a unified Italy, but he did long display a desire to see a powerful, industrialized, and ultimately conservative political entity emerge on the Italian peninsula, and acted accordingly. I’ve examined these motivations and their origins in the answer below which I will admit might have gotten a little out of hand.
Beginnings
The Camillo Benso, like many other 19th-century Italian intellectuals of the "Liberal" persuasion, was principally influenced by the French republican thought of of the Napoleonic era. His father, Michele Benso, had been a Piedmontese defector to the French Revolutionary army and grew to become a close friend and collaborator of Napoleon Bonaparte's brother-in-law, Prince Camillo Borghese. The Prince Borghese himself, while an immeasurably wealthy Roman aristocrat, harbored radical ideals and had enrolled in the French Revolutionary Army at the age of twenty-one, rising through the ranks of newly meritocratic army and eventually entered Napoleon's inner circle where he was introduced to the (by then) Emperor's sister, Pauline. The elder Count of Cavour’s experiences as Prince Borghese’s aid under Napoleon’s rule would have a defining impact on the values he transmitted to his son.
In all of Napoleonic Europe, a class of new men emerged as cogs in the new Imperial administrative machine. Replacing the largely privilege-based administrative system which had prevailed in most of Europe, the Napoleonic administration selected scores of well-educated middle-class men and appointed them to important administrative roles in a government apparatus that was much larger than that which had ever previously existed in Europe. Indeed, from taxation, to policing, to roads and highways, Napoleon's legal and administrative reforms lay the bedrock from which the government apparatus of many Western European governments would go on to be built. The Prince Borghese, while from a much different background than the more typically middle-class appointees, was one such beneficiary of Napoleon's administrative machine, having been appointed Governor-general of the parts of Northwest Italy which had been directly annexed by France (Gouverneur-général des départements au-delà des Alpes). A more typical beneficiary was the Benso family, with Michele Benso rising through the ranks of the Napoleonic army before joining Prince Borghese’s governmental staff in Turin (his son Camillo, whom we will get to in a moment, was in fact named after the Prince Borghese, with whom Michele enjoyed a strong rapport and the Prince even stood as his godfather).
While the Benso were aristocratic landowners (they were Counts of Cavour, after all) who were better off than most, they ranked low on the aristocratic ladder and were not particularly wealthy (Michele earned more from his marriage to Swiss heiress Adèle Sellon d'Allaman than he ever did from his estates) so the Napoleonic system had allowed Michele to rise much higher than he could have ever have aspired to otherwise. Predictably, he suffered an enormous fall in status during the Post-Napoleonic Restoration and in spite of his best efforts to ease himself back into high society, he would only ever hold political office at the municipal level thereafter. It’s important to keep in mind that after Napoleon’s fall, there existed countless members of the bourgeoisie and lower aristocracy whose fortunes were identical to those of Michele Benso, finding themselves suddenly cut off from power and political representation. This frustrated social class would lay the groundwork for the events which would unravel later in the century.
As the French empire was dismantled all over Europe (and its ideals suppressed by reactionary policies) the continent’s growing bourgeoisie only became more restless as new technologies, social changes, and economic shifts ensured that society would nonetheless continue to rapidly change in spite of the restoration of reactionary and repressive governments all over Europe. Camillo Benso, like his father Michele decades prior, was quick to align himself within this rapidly changing panorama — as were similarly positioned bourgeois intellectuals all over Italy. Predictably, unrest and disdain for the restored monarchies (in addition to economic contraction) characterized the early restoration period. Thus while the restored Kingdom of Piedmont-Sardinia in which Camillo Benso lived had originally positioned itself as a reactionary polity which opposed many of the things that Napoleon had introduced, over the years the small kingdom’s ruling House of Savoy found it necessary to adopt a combination of concessions and appeasements towards the growing bourgeoisie in an attempt to keep them loyal. Camillo Benso himself was appointed mayor of his hometown at the age of twenty-two, on the one hand in recognition of his academic achievements while at the Military Academy in Turin (he was still studying to join the Corps of Engineers) while on the other guaranteeing he wouldn’t actually serve in the Army for long once he graduated (he was already displaying a predisposition for some radical ideas — and his political appointment stopped him from radicalizing other people in the army while hopefully also going some ways to mop up any resentment he might still have for his father’s fall from grace).
Camillo Benso's life as a young man continues after the jump