First, a reasonably coherent definition of pivotal -quantity._ not "statistic",
quoted from Wikipedia:
In statistics, a pivotal quantity or pivot is a function of observations and unobservable parameters such that the function's probability distribution does not depend on the unknown parameters (including nuisance parameters).[1] A pivot quantity need not be a statistic—the function and its value can depend on the parameters of the model, but its distribution must not. If it is a statistic, then it is known as an ancillary statistic.
Often pivotal quantities are used to find confidence intervals. It is convenient to have a quantity that involves a sufficient statistic and the parameter to be estimated, such that the quantity has a known distribution for a knows value of the parameter.
As you say, when $\sigma$ and $n$ are known and data are normal with unknown mean $\mu,$ we have the quantity $Z=\frac{\bar X - \mu}{\sigma/\sqrt{n}}\sim \mathsf{Norm}(0,1).$
This quantity contains one unknown parameter $\mu$ and the statistic $\bar X,$ which is a reasonable estimator of $\mu.$ Then we can write
$$ P\left(-1.96 <\frac{\bar X - \mu}{\sigma/\sqrt{n}} <1.96\right) = 0.95,$$
because we know the quantity $Z \sim \mathsf{Norm}(0,1).$
Then with a little algebra 'called pivoting' we can isolate $\mu$ to get
$$P\left( \bar X - 1.96\frac{\sigma}{\sqrt{n}} < \mu <
\bar X + 1.96\frac{\sigma}{\sqrt{n}} \right) =0.95.$$
Thus, a 95% confidence interval for $\mu$ is of the form
$$ \left(\bar X - 1.96\frac{\sigma}{\sqrt{n}},\;
\bar X + 1.96\frac{\sigma}{\sqrt{n}}\right).$$
Now for an example you may not have seen before. Suppose you have $n$ random observations $X_i$ from the exponential distribution
$\mathsf{Exp}(\mathrm{rate}=\lambda),$ so that $E(X_i) = 1/\lambda = \mu.$ One can show that $\frac{\bar X}{\mu}\sim
\mathsf{Gamma}(\mathrm{shape}=n,\mathrm{rate}=n).$
The MLE of $\mu$is the sample mean $\bar X.$ Also, $\bar X/\mu$ is a pivotal quantity, which can be used to get a confidence interval for $\mu.$
One can find constants $L$ and $U$ that cut respective probabilities
$0.025$ from the lower and upper tails of $\mathsf{Gamma}(n,n)$ so that $$P\left(L < \frac{\bar X}{\mu} < U\right) = 0.95.$$
Upon pivoting. we have $P(\bar X/U <\mu < \bar X/L) =0.96$ and thus
the 95% confidence interval $(\bar X/U, \bar X/L)$ for $\mu.$