Help with query.

Question

Help with query.

p.shabbir

SSCrazy

Points: 2404
More actions
August 8, 2016 at 5:53 am

#400540

Hi All,
Please help me.I have table "Email" like below.
In case someone register for both email and SMS at the same time, two lines will be reported in the file. One for Email and one for SMS ( 3rd row in the sample data)
either "SMS" or "Email". Email, if the change is on OptinEmailCitadium / SMS, if the change is on OptSMSCitadium
CREATE TABLE #TEST (OptinEMAILCitadium INT,OptinSMSCitadium INT)
And sample Data
INSERT INTO #TEST
SELECT 1,0
UNION ALL
SELECT 0,1
UNION ALL
SELECT 1,1
UNION ALL
SELECT 0,0
UNION ALL
SELECT 1,0
OptinEMAILCitadiumOptinSMSCitadium
10
01
11
00
10
Need O/p Like below
OptinEMAILCitadiumOptinSMSCitadiumType
10Email
01SMS
11Email
11SMS
00SMS
10Email
Thanks in advnc.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

I'm sure that you have some ID for each person that would work as a primary key. That's the reason I added it to the example.

You just need to unpivot the data and one way to do it is by using the APPLY operator.

Check this and ask questions that you might have.

CREATE TABLE #TEST (SomeoneID int, OptinEMAILCitadium INT,OptinSMSCitadium INT)

INSERT INTO #TEST

SELECT 1,1,0

UNION ALL

SELECT 2,0,1

UNION ALL

SELECT 3,1,1

UNION ALL

SELECT 4,0,0

UNION ALL

SELECT 5,1,0

SELECT SomeoneID,

OptinEMAILCitadium,

OptinSMSCitadium,

ISNULL( [Type], 'None') AS [Type]

FROM #TEST

OUTER APPLY(SELECT 'Email' AS [Type]

WHERE OptinEMAILCitadium = 1

UNION ALL

SELECT 'SMS'

WHERE OptinSMSCitadium = 1) AS unpiv

--ORDER BY SomeoneID;

GO

DROP TABLE #TEST

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

p.shabbir SSCrazy Points: 2404 More actions · Answer 2

Luis Cazares (8/8/2016)
I'm sure that you have some ID for each person that would work as a primary key. That's the reason I added it to the example.
You just need to unpivot the data and one way to do it is by using the APPLY operator.
Check this and ask questions that you might have.
CREATE TABLE #TEST (SomeoneID int, OptinEMAILCitadium INT,OptinSMSCitadium INT)
INSERT INTO #TEST
SELECT 1,1,0
UNION ALL
SELECT 2,0,1
UNION ALL
SELECT 3,1,1
UNION ALL
SELECT 4,0,0
UNION ALL
SELECT 5,1,0
SELECT SomeoneID,
OptinEMAILCitadium,
OptinSMSCitadium,
ISNULL( [Type], 'None') AS [Type]
FROM #TEST
OUTER APPLY(SELECT 'Email' AS [Type]
WHERE OptinEMAILCitadium = 1
UNION ALL
SELECT 'SMS'
WHERE OptinSMSCitadium = 1) AS unpiv
--ORDER BY SomeoneID;
GO
DROP TABLE #TEST

Perfect Luis, Many Thanks.

We do have a memberID key column.

Can you please explain about Apply operator how does it works? Just in a simple words.

Thnx again.