Accordingly to suggestion by MadMyche in solution #1...
Try this:
SELECT UserId, [date], [O] AS [O(Open)], [C] AS [C(Click)], [P] AS [P(Purchase)], TRACKING, zip, [Subject]
FROM
(
SELECT UserId, [action], TRACKING, zip, [Subject]
FROM clients
) DT
PIVOT(COUNT([UserId]) FOR [action] IN([O], [C], [P])) PVT