Suggestions/Help With SQL Data Manipulation

• Todd Young-359443

  Old Hand

  Points: 345

  More actions

  December 29, 2015 at 8:23 am

  #310567

  I am trying to get a poorly designed table into a more of a pivot format. In simplistic terms, the table looks

  like this:

  declare @input table (RowID int IDENTITY(1,1) NOT NULL, Memberid int, ActionID int, ActionDate datetime)

  It is used as enrollment table, where ActionID of "1" is an Enrollment and "2" is a Dis-enrollment.

  All data is placed in the table sequentially, so even if there are multiple entries for the same member

  for the same date the identity column will have the correct order of events.

  I am trying to get this data into a table that looks like this:

  declare @formated table ( RowID int IDENTITY(1,1) NOT NULL, Memberid int, Enrolldate Datetime, DisenrollDate Datetime)

  Populating the enrolldate column is a clean start but trying to match the disenrolldate (which could be null)

  with each row has been a bugger. I've been playing around trying to find a straightforward solution, but can't

  seem to get my arms around it.

  Would any SQL experts be willing to give me a hand ??

  Setup Code follows..

  declare @input table (RowID int IDENTITY(1,1) NOT NULL, Memberid int, ActionID int, ActionDate datetime)

  INSERT INTO @input

  --

  Select 31,1, '06/14/2013' Union All

  Select 31,2, '06/17/2013' Union All

  Select 31,1, '06/18/2013' Union All

  Select 31,2, '06/18/2013' Union All

  Select 31,1, '06/24/2014' Union All

  Select 31,2, '07/05/2013' Union All

  --

  Select 53,1, '11/11/2013' Union All

  --

  Select 56,1, '11/04/2013' Union All

  Select 56,2, '02/03/2014' Union All

  Select 56,1, '06/03/2014' Union All

  Select 56,2, '09/03/2014' Union All

  Select 56,1, '01/01/2015' Union All

  --

  Select 116,1, '06/04/2015' Union All

  Select 116,2, '07/01/2015' Union All

  Select 116,1, '07/01/2015' Union All

  Select 116,2, '07/01/2015' Union All

  Select 116,1, '08/11/2015' Union All

  --

  Select 118,1, '07/07/2015' Union All

  Select 118,2, '07/07/2015' Union All

  --

  Select 186,1, '12/24/2015' Union All

  Select 186,2, '12/24/2015' Union All

  Select 186,1, '12/24/2015'

  --select * from @input

  declare @formatted table ( RowID int IDENTITY(1,1) NOT NULL, Memberid int, Enrolldate Datetime, DisenrollDate Datetime NULL)

  INSERT INTO @formatted

  SELECT MemberID, Actiondate, NULL from @input

  where ActionID = 1

  order BY Memberid, RowID

  --select * FROM @formatted

  --

  -- Now What ??

  --

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  December 29, 2015 at 8:51 am

  #1848520

  If you're sure that there won't be any missing/duplicate entries (two consecutive enroll dates without disenroll dates or viceversa), this can do the trick.

  WITH cte AS(

  SELECT MemberID, Actiondate, ActionID,

  (ROW_NUMBER() OVER( PARTITION BY Memberid ORDER BY RowID) + 1) / 2 rn

  from @input

  )

  INSERT INTO @formatted(

  Memberid,

  Enrolldate,

  DisenrollDate

  )

  SELECT Memberid,

  MAX( CASE WHEN ActionID = 1 THEN Actiondate END),

  MAX( CASE WHEN ActionID = 2 THEN Actiondate END)

  FROM cte

  GROUP BY Memberid, rn

  ORDER BY Memberid, rn;

  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

• Todd Young-359443

  Old Hand

  Points: 345

  More actions

  December 29, 2015 at 9:25 am

  #1848536

  Looks like that should do it .. exactly was I was trying to accomplish.

  Thank you very much !!!