Convert rows into columns

Question

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 2-Apr-20 11:50am

learning_new

Updated 11-Apr-20 2:10am

Add a Solution

3 solutions

Solution 1

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[^]

Posted 2-Apr-20 12:03pm

MadMyche

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

:)

Solution 3

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];

Posted 11-Apr-20 2:11am

NEGIN_K

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2020-04-02T23:47:00

Solution 2

Accordingly to suggestion by MadMyche in solution #1...

Try this:

SQL

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

Posted 2-Apr-20 23:47pm

Maciej Los

Comments

MadMyche 3-Apr-20 8:10am

+5

Maciej Los 3-Apr-20 8:30am

Thank you.