Click here to Skip to main content
14,694,027 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a table called clients where I track the email sent to people and it looks this.

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

cbg10        O        4/18/2020           TG           67548       Summer arrives

cbg10        P        4/18/2020           TG            67548      Summer arrives

cbg10        C        4/18/2020           TG            67548       Summer arrives


This what I hope the table would look like: As you can this person open the email 2 times. Instead of 2 rows,like in the first table this one combine the actions taken
Userid     date      O(Open)  C(Click)  P(Purchase)  TRACKING  zip     Subject

cbg10    1/2/2020     2         1        1            Amz     30105    $1 Sales journey
cbg10    4/2/2020     1         1        1            Amz     30105    Summer arrives
<

What I have tried:

This is what I tried, unfortunately, it gives me a row for each open. So, if someone open an email 3 times, it will give me 3 rows instead of 1 rows that shows in columns O(open) 3:
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]
Posted
Updated 11-Apr-20 3:10am

What you are looking for most likely is going to be a good reason to learn how to use the PIVOT functionality in SQL Server.

References:
SQL Server PIVOT Operator Explained Clearly By Practical Examples[^]

Using PIVOT and UNPIVOT - SQL Server | Microsoft Docs[^]
   
Comments
Maciej Los 3-Apr-20 4:58am
   
5ed!
MadMyche 3-Apr-20 10:01am
   
Thank you, and reciprocated
Maciej Los 3-Apr-20 10:22am
   
:)
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
    --WHERE [date] BETWEEN '...' AND '...'
) DT
PIVOT(COUNT([UserId]) FOR [action] IN([O], [C], [P])) PVT
   
Comments
MadMyche 3-Apr-20 8:10am
   
+5
Maciej Los 3-Apr-20 8:30am
   
Thank you.
select UserId,
SUM(case when [action] = 'O' then 1 else 0 end) as [O(Open)],
SUM(case when [action] = 'C' then 1 else 0 end) as [C(Cash)],
SUM(case when [action] = 'P' then 1 else 0 end) as [P(Paid)],
[Date],
Tracking,
Zip
from Clients
GROUP BY UserId, [Date], [Tracking], [Zip];
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900