SPI and multiple devices

Yes, in theory "any GPIO" would do; this is called "software chip select"

But many SPI host engines also offer a "hardware chip select" option to control a dedicated pin from the SPI engine itself, which is a hair more efficient and keeps the software simpler. (It might also tie in with alternate uses of a synchronous serial engine, for example I2S where the "chip select" becomes more of a left/right channel toggle).

You should be able to use "software chip select" in your Raspberry-pi based project; likely you can do so through the SPI driver though that depends a little on precisely which one you are using.

To take things to an absurd extreme, you could even use something like a 74HC139 3-of-8 decoder to drive your chip selects from fewer GPIOs; but in real systems you'd typically run into fan-out loading and related signal integrity issues before you ran out of GPIOs, though you could add bus buffers...

A last caution: it has been claimed the SD cards operating in SPI mode are ill-behaved not-quite SPI peripherals, so you may want to avoid having anything sharing an SPI bus with one. Of course on a pi, your main SD card is local, and for an auxiliary you should consider a higher-bandwidth USB connection anyway.

For what it's worth, I struggle to recall ever using hardware chip select in an MCU project, but I've almost always used it with Linux hosts, both because the driver made it easy, and because the SPI often also had another role such as providing a flash file system, for which the default configuration already used hardware select, and while hybrid solutions are possible a uniform one will be simplest.

SPI slaves need a dedicated CS input pin, as it controls the hardware of SPI peripheral to either receive or ignore transmissions on bus.

SPI masters typically can use any GPIO pin to control which slave is active, but if SPI master has a overly complex SPI peripheral that supports hardware CS timing control, then it is usually a dedicated CS output pin.