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.

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  August 8, 2016 at 6:40 am

  #1894315

  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

  August 8, 2016 at 6:58 am

  #1894322

  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.