Zechariah 12:10 has a variety of translations and interpretations.
1. Let's look at the King James Version:
"And I will pour upon the house of David, and upon the inhabitants of Jerusalem, the spirit of grace and of supplications: and they shall look upon me whom they have pierced, and they shall mourn for him, as one mourneth for his only son, and shall be in bitterness for him, as one that is in bitterness for his firstborn."
According to this translation, the person who is pierced is the one identified as "me." According to interpreters, this "me" could be God, who endured continual indignity from the Israelites. According to this view, Israel feels bad about her past treatment of God, particularly after God is nice enough to restore her and defeat her enemies. Or "me" may refer to the prophet, who experienced persecution in Israel. Once his message is fulfilled, however, his Israelite persecutors change their tune.
2. The Jewish Publication Society offers another translation:
"But I will fill the House of David and the inhabitants of Jerusalem with a spirit of pity and compassion; and they shall lament to me about those who are slain, wailing over them as over a favorite son and showing bitter grief as over a first-born" (NJPS).
In this translation, "me" is not the one who is pierced, but the piercing happens to someone else.
The New Revised Standard Version takes this one step further:
"And I will pour out a spirit of compassion and supplication on the house of David and the inhabitants of Jerusalem, so that, when they look on the one whom they have pierced, they shall mourn for him, as one mourns for an only child, and weep bitterly over him, as one weeps over a firstborn."
The NRSV removes "me" altogether. And, according to the Biblical Hebraica Stuttgartensia, there is a variant of the text that does precisely that.
So who is the pierced person in this scenario? Ehud ben Zvi offers the following comments in the Jewish Study Bible:
"The Hebrew is ambiguous, because it may refer to a person or a group whom they have pierced...[I]t is more likely that it refers to an individual or group within the nations. For an understanding of the verse as pointing to the Messiah from the House of Joseph, see b. Sukkah 52a. Radak reads the text differently; for him it describes such a salvation that if even one person of Israel were killed in the battle, they will be astonished."
According to ben Zvi, the object of mourning in Zechariah 12:10 can be individual or collective. Many Jewish interpreters have applied it to the Messiah of the House of Joseph, a Messiah of Israel who will get killed in God's battle against the nations. He is distinct from the Messiah of the House of David, the one who will rule Israel in the Messianic Age.
Or, for Radak, the pierced one can refer to the Israelite casualties in the battle against the Gentiles. But these interpretations distinguish the "they" who mourn from the "they" who pierce. This is possible, but Zechariah 12:10 doesn't explicitly mention the Gentiles fighting back against the Israelites and piercing them.
And then there are other interesting interpretations. In b. Sukkah 52a, the text that ben Zvi cites, another candidate for the pierced one is the evil inclination within humans. Rabbi Judah explains:
" In the time to come, the Holy One, blessed be He, will bring the Evil Inclination and slay it in the presence of the righteous and the wicked. To the righteous it will have the appearance of a towering hill, and to the wicked it will have the appearance of a hair thread. Both the former and the latter will weep; the righteous will weep saying, 'How were we able to overcome such a towering hill!' The wicked also will weep saying, 'How is it that we were unable to conquer this hair thread!'" (translation is from my Judaic Classics Library).
So, in this view, God will kill the inclination within humans to sin. Some will marvel that they were ever able to conquer what seemed to be insurmountable temptations, while others will regret that they caved into sin too easily.
And then there is the view that the pierced one of Zechariah 12:10 is Jesus Christ (see John 19:37; Revelation 1:7). This gets played out in a few different ways. Some believe that Zechariah 12:10 began to be fulfilled in Luke 23:48 and Acts 2. In Luke 23:48, many people at the crucifixion smote their breasts, probably in repentance for their role in Jesus' death. And, in Acts 2, Jews who were involved in Jesus' crucifixion (either directly, or through Israel's overall rejection of him) repented in his name and received baptism.
Another scenario is that people will mourn at Christ's return. Ellen White, the founder of Seventh-Day Adventism, posits in The Great Controversy that God will resurrect the very people involved in Jesus' crucifixion (e.g., Herod, Pilate, Caiaphas, etc.) right at the Second Coming, allowing them to see the triumph of the one they killed. And many dispensationalists apply Zechariah 12:10 to Israel's conversion to Christ at his return.
But I have a question about applying Zechariah 12:10 to the Messiah, the son of David: Doesn't Zechariah 12 say that the House of David will mourn for the one who is pierced? My impression (which could be wrong) is that Zechariah distinguishes the pierced one from the Davidic line.
I have another interpretation: Could the Israelites be weeping for all of the Gentiles they killed in the eschatological battle? In Zechariah 12:8-9, God talks about making even the weakest Jerusalemites like David and slaying the Gentiles who attack God's city. Perhaps the pierced ones are those very Gentiles. God may want Israel to mourn for them in recognition of their (the Gentiles') humanity. In this view, God did not want to kill the Gentiles, but he had to do so because of their aggression against God's people.
In Zechariah 14, we read of the Gentiles coming to Jerusalem to worship God, so God may want to impress upon the Israelites that he loves everyone, Israelite and Gentile. If the Israelites are to accept the Gentiles who come to Jerusalem, then they must honor their humanity. And one way for that to happen is for them to realize that the people they killed in battle were human beings, with their own thoughts, feelings, dreams, and people who cared about them. The Gentiles' deaths may have been necessary, but they were still tragic.
Midrash Avkir and b. Sanhedrin 39b, both Jewish sources, have a story about the Red Sea. When the Egyptians drowned, the angels wanted to sing for joy, but God stopped them. "The works of my hands are drowning in the sea, and you would utter song in my presence?" (translated by William Braude). According to this midrash, God loves even the people he has to destroy. And the point of Zechariah 12:10 may be that God will enable the Israelites to see their enemies as God sees them.