Help with SQL query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with SQL query

Author

Topic

wolfeyes333
Starting Member

3 Posts

Posted - 2014-12-21 : 08:58:44

I am currently doing a sql query where it returns the emails and the action (click,open,unsub. so I'll have multiple rows for 1 email showing it's history, I want to make it so that each email is in 1 row, and have columns for click open and unsub showing the percentages which is more logical.

The question is how can I do this, this is my first time posting on the site so any help is appreciated

wolfeyes333
Starting Member

3 Posts

Posted - 2014-12-21 : 09:03:54

This is an example of the two row returned:

abigaile@abigailehuller.com unsub
abroyalle@charter.net open
abroyalle@charter.net unsub
accent@mcleodusa.net click
ace@voyager.net open
ace@voyager.net unsub
acevedhle@ocps.net open
acevedhle@ocps.net unsub
acevedhle@ocps.net open
acevedhle@ocps.net unsub
acevedhle@ocps.net open
acevedhle@ocps.net open
acevedhle@ocps.net open
acevedhle@ocps.net unsub
acevedhle@ocps.net unsub
acevedhle@ocps.net open
achunghl@kebus.com open
achunghl@kebus.com open
aculberson@republicind.com open
ada.liu@signettour.com open
adabasso@sonfastcaribe.com open
adelle@coastaldatasystems.com unsub
adelle@coastaldatasystems.com unsub
adelle@coastaldatasystems.com open
adelle@coastaldatasystems.com open

As we can see multiple rows for the same email showing it's history, what I would like to see if have a table with columns:

email, open %, click, %, unsub%

Was wondering if I can do this in SQL

Thank you for your time.

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-12-23 : 06:37:07

You need something like this combo...

SELECT
LEFT('abigaile@abigailehuller.com unsub',
CHARINDEX(' ','abigaile@abigailehuller.com unsub')-1) 'Email', -- find left string

RIGHT('abigaile@abigailehuller.com unsub', LEN('abigaile@abigailehuller.com unsub')-
CHARINDEX(' ','abigaile@abigailehuller.com unsub')) 'Open %' -- find right string

We are the creators of our own reality!

wolfeyes333
Starting Member

3 Posts

Posted - 2014-12-24 : 10:35:56

Thanks for the update, I will definately try this out.

Sorry for the late response.

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-12-24 : 11:27:35

No worries, remember you need to replace the text 'abigaile@abigailehuller.com unsub' with you actual column header name and drop the single quotes.

Also, you will probably need to combine with a CASE statement if you want to filter on the other names as column data.

We are the creators of our own reality!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources