Convert rows into columns and group some colums

Question

I saw a lot of example on how to convert rows into columns and I was able to accomplish that.Here is the table that have:

Userid     action      date            TRACKING         zip        Subject
cbg10        O        1/2/2020           Amz           30105       $1 Sales journey
cbg10        O        1/2/2020           Amz           30105       $1 Sales journey
cbg10        C        1/2/2020           Amz           30105       $1 Sales journey
cbg10        P        1/2/2020           Amz           30105       $1 Sales journey
kel64        O        1/2/2020           Amz           45685       $1 Sales journey
kel64        O        1/2/2020           Amz           45685       $1 Sales journey
kel64        O        1/2/2020           Amz           45685       $1 Sales journey
kel64        C        1/2/2020           Amz           45685       $1 Sales journey 
kel64        C        1/2/2020           Amz           45685       $1 Sales journey
kel64        p        1/2/2020           Amz           45685       $1 Sales journey
cbg10        O        4/18/2020          TG            30105       Summer arrives
cbg10        P        4/18/2020          TG            30105       Summer arrives
cbg10        C        4/18/2020          TG            30105       Summer arrives

This what I hope the table would look like: As you can see some open open the email 2 times. Instead of having 2 rows like in the first table this one add the actions taken by the person by subject

Userid     date      O(Open)  C(Click)  P(Purchase)  TRACKING  zip     Subject
cbg10    1/2/2020     2         1        1            Amz     30105    $1 Sales journey
kel64    1/2/2020     3         2        1            Amz     45685    $1 Sales journey
cbg10    4/2/2020     1         1        1            Amz     30105    Summer arrives

This is the code that I have, but it is not giving me what I need
select UserId,

max(case when Aciton = 'O' then 1 else 0 end) as [O(Open)],
max(case when Aciton = 'P' then 1 else 0 end) as [P(Paid)],
max(case when Aciton = 'C' then 1 else 0 end) as [C(Cash)],
 [Date], 
 Tracking,
 Zip
 from myTable
 GROUP BY UserId, [Date], [Tracking], [Zip]
 ORDER BY UserId

Answer 1

Thankfully SQL Server has a built in function called a PIVOT that should do exactly what you are looking for. There is a great article at SQLServerTutorial.Net which has some great examples on how to use this.

I think that this query should give you what you are looking for. I built a table variable called @Table for the purpose of my testing.:

SELECT UserID,
[Date],
[O] AS 'O(Open)', --The value in the [Action] Column called out below, becomes the column name up here
[C] AS 'C(Click)', --The value in the [Action] Column called out below, becomes the column name up here
[P] AS 'P(Purchase)', --The value in the [Action] Column called out below, becomes the column name up here
Tracking,
Zip,
[Subject]
FROM
(
    SELECT UserID,
    [Action],
    [Date],
    Tracking,
    Zip,
    [Subject]
    FROM @Table
) T
PIVOT
(
    COUNT([Action])
    FOR [Action] IN([O], [C], [P]) --Call out the values in the Action Column
) AS Pivot_Table

For Me It Produced a Table Like Below:

UserID  Date        O(Open) C(Click)    P(Purchase) Tracking    Zip     Subject
cbg10   2020-01-02  2       1           1           Amz         30105   $1 Sales journey
cbg10   2020-04-18  1       1           1           TG          30105   Summer arrives
kel64   2020-01-02  3       2           1           Amz         45685   $1 Sales journey

I can break this down in a little more details if you need me to, since there are several nuances in what you asked for, grouping by multiple columns, mostly.

Hope this helps!