This is a pretty complicated question, and there's some debate as to what caused the shift in public opinion that led to prohibition's repeal. In general, though, most historians agree that these things played an important role:

• Prohibition didn't stop people form drinking. Too many people ignored or actively flouted the law, leading to a surge in criminal activity (bootlegging), police corruption, etc. In many places and social circles, it was an open secret that huge numbers of people drank despite prohibition, which begged the question of why the law was there at all.
• The Depression. One of the main arguments for repeal was that if the sale of alcohol was legalized, it could be taxed (increasing government revenue), and would create economic growth/jobs. After 1929 this was a very compelling argument for a lot of people.
• The "Dry" political coalitions that had pushed for prohibition broke down. Prohibition had been made possible by a very broad, loose political alliance between christian groups, women's groups, progressives and others - but by the time prohibition was being questioned and lawmakers were considering repeal, that coalition had broken down; women had won the vote, and some activists had begun to question whether prohibition was accomplishing what they'd hoped it would - or whether it might just be breeding disrespect for the law, or even encouraging illicit drinking. As a result, the large, vocal coalition that had been there to push for prohibition simply wasn't there to defend it during the debate over repeal.
• Drinking culture changed. Before prohibition, Americans tended to consume alcohol in gender-segregated spaces, where men drank in saloons and women drank privately at home. Male drinking was the specific problem that prohibition targeted. But once the (legal) saloons closed, Americans started drinking at home and in speakeasies - in a hetrosocial context where the focus was on the company and the "fun" of it as much as it was on getting blind drunk. Prohibition had altered how and why, and in what context, Americans drank - and this made repeal more palatable. Once the male saloon-drinking culture that had been the primary reason for prohibition had been eliminated, in other words, more people were willing to consider repeal.

Daniel Okrent's Last Call is a decent pop-history introduction to this if you'd like to read more. David Kyvig's Repealing National Prohibition is kind of old now but as far as I know it's still the most in-depth study of the political movement for repeal available. Catherine Murdock's Domesticating Drink is the go-to source for the change in male/female drinking cultures during Prohibition. Oh and Lori Rotskoff's Love on the Rocks is more about the aftermath of that, but has a very good chapter on prohibition and repeal as well iirc.