Interesting question from the OP, and certainly started a lively discussion. Up until early last year, I was part-owner of a CD store that specialized in classical music, and from that perspective I think there are some factors that haven't yet been addressed in this discussion, namely the role of the record companies and copyright holders in the process of disseminating and marketing their products, and how they have contributed to what may be the demise of the industry. The old model of the leviathan record company issuing a succession of new recordings for mass consumption is fast becoming obsolete. Whether this is a good thing or not can be hotly debated--God knows these companies have often not behaved well--but the end result is that with new technologies it becomes harder for them to control the marketing and distribution process. A number of artists have taken to recording and issuing their own CDs independently. No, they're not going to make a gazillion dollars on royalties this way, but disc-for-disc, they'll sure do better for themselves than a record company would. I know of at least two important classical labels that don't pay royalties at all, they just purchase the recording for a set fee and that's it. The artist doesn't even own his/her own copyright. It was precisely this behavior that spawned sites like Magnatune and CDBaby, where an artist can distribute an independent project and reap rewards for it.
What I witnessed during my 20 (or so) years associated with the sales end of the industry was an inexorable shrinking of both the market for CDs and the number of new titles made available from the major labels, which also means they pretty much stopped promoting their artists. The record companies themselves went through disastrous mergers (like Sony/BMG or Time-Warner/AOL) and in the process lost their way forward. One of the oldest and most venerable of the classical labels, EMI, basically doesn't exist any more because of how the company was broken up and sold a few years ago. A lot of world-class performers were simply dropped by their former labels, but these same labels are endlessly repackaging and re-releasing their back-catalog. This process actually began in the late 1990s, but in recent years it has accelerated to the point where the old model of the CD store where you can go to browse, socialize, learn, etc. is an endangered species.
I still prefer to own a physical object (CD, LP) because I would rather have control over my own listening experience and not surrender it to a remote server, like a streaming service, whose reliability may be uncertain and who's probably going to track my habits. Also, the purchase of a CD or LP (if new and not used) should be (but isn't in every case) a way of supporting the artist or at least the artist's record label. If properly controlled, streaming should also do this, but recalling some of the earliest forays into music-sharing (remember Napster?), these were simple rip-offs of copyrighted recordings made possible by new technologies.
So, to address the original question, CDs are probably going to be with us for some time to come, but the way they are marketed is changing. I suppose eventually they'll go the way of the piano-roll and wax cylinder, but even these have not totally disappeared yet. They're generally regarded as museum-pieces though.
Okay, sorry for the length of this rambling post--in fact, there's even more to say. To quote one of those 19th-century French authors: "If I'd had more time, I'd have written a shorter one."
