The Cardinals received infielders Yairo Munoz and Max Schrock in exchange for Piscotty, who had nine home runs and 39 RBIs in 107 games last year.
Piscotty, who played at Stanford and grew up in nearby Pleasanton, California, missed some time last season to be back with his family in California after his mother was diagnosed with ALS.
His name had surfaced in recent days in reports that said the Cardinals were open to trading him to the A's or Giants.
"The thought of playing at home is a dream, and to be close to my mom and family is priceless," Piscotty told the San Francisco Chronicle. "I'm excited to go somewhere I'm wanted. I hear great things about Oakland's young core, and I'm excited to be a part of that.
"I grew up an A's fan and have gone to games since I was an infant. Seems meant to be on multiple levels. I'm looking forward to 2018."
John Mozeliak, president of baseball operations for the Cardinals, told St. Louis media that the trade wasn't "simply for geographic or sentimental reasons" but acknowledged that Piscotty's family situation was considered.
"It had to be something that made sense for us," Mozeliak said. "There were certainly some opportunities to move him elsewhere. When you are looking at how to break a tie, clearly that did play into it."
Piscotty, 26, made his debut for St. Louis in 2015 and finished sixth in the NL Rookie of the Year voting after hitting .305 in 63 games. In three major league seasons, he has a .262 batting average, 38 home runs and 163 RBIs.
He signed a $33.5 million, six-year contract extension in the offseason that runs through 2022.
Munoz, 22, hit .300 with 13 homers and 68 RBIs this year for Double-A Midland and Triple-A Nashville. Schrock, 23, batted .321 with seven homers and 46 RBIs for Midland, and was a Texas League All-Star.
The Associated Press contributed to this report.