when people were excited to get a Solaris SPARC workstation.
They weren't excited because it was a SPARC workstation. They were excited because it was a Solaris workstation. For much of the 1990s, and even into the early 2000s, Solaris provided perhaps the best workstation OS experience around. It had the best desktop environments, it had the best userland software, it had excellent development tools, it had a lot of advanced functionality, and compared to its contemporaries it was very pleasant to use. Although Solaris workstations weren't exactly cheap, they were relatively affordable to serious users.
Yes, part of Solaris' appeal was due to its tight integration with the hardware, but the hardware itself was largely irrelevant to most users. It was the Solaris experience that they wanted and desired.
NeXT systems running NeXTSTEP were a real competitor to Solaris and SPARC workstations, but NeXT systems were prohibitively expensive for even many deep-pocketed business users
As I recall, Next machines were actually cheaper than contemporary Sparc Stations but Next was targeting users who were mostly unwilling to pay so much. With rare and mostly weird exceptions* Sparcstations were not used by nor priced for rank and file paper pushers. These were engineering workstations. They were use for tasks beyond the reach of contemporary PC's. In the beginning this was about raw computational power and 32-bit addressing when PC's were 16-bit. In the mid to late 90's, it was 64-bit addressing. PC's were catching up in raw power but they were still 32-bit. Unix wasn't given much thought. All engineering workstations ran some form of Unix. Sparc machines were never fully on top in either hardware or OS. Alphas were faster. IRIX had a better gui. Neither was a well supported by third parties. Then, as now, if too you need is only available for vendor A's systems, you buy vendor A's systems. It doesn't matter that vendor B's machines are faster or vendor C's machine have a nicer OS.
The big change was 64-bit PC's. x86's machines had already passed Sparc in performance in the push to 1 Ghz. But the lack of 64-bit addressing meant that couldn't be used for the big jobs that really needed the performance. Once that obstacle was removed, there was a huge push to move everything to PC's. Software vendors followed because their customers demanded it.
*In the mid-90's, Charles Schwab tried to replace all PC's with relatively cheap Sparc Classics. It was an attempt to reign in risky behavior by their employees by giving them computers that were not only locked down but physically incapable of running software they may have brought from home. It whole operation failed miserably.