"Vertical" COALESCE ?!?

• kappa

  SSC Veteran

  Points: 259

  More actions

  June 4, 2008 at 2:42 pm

  #71429

  Hello,

  Here's my table:

  Create table Test(Row1 varchar(50), Row2 varchar(50))

  Insert into Test(Row1) values('1')

  Insert into Test(Row1) values('John')

  Insert into Test(Row1) values('1234567')

  Insert into Test(Row2) values('2')

  Insert into Test(Row2) values('Peter')

  Insert into Test(Row2) values('9876543')

  So, I end up with:

  Row1 Row2

  ---------------- -------------------

  1 NULL

  John NULL

  1234567 NULL

  NULL 2

  NULL Peter

  NULL 9876543

  My output needs to look like this (non-NULL values from columns ONLY):

  Row1 Row2

  ---------------- ----------------

  1 2

  John Peter

  1234567 9876543

  Question: HOW?

  Thanks in advance.

• Jeffrey Williams

  SSC Guru

  Points: 90350

  More actions

  June 4, 2008 at 3:51 pm

  #824578

  How do you know what value in Row2 to associate with the value in Row1? How do you know that the value 2 belongs to the value 1 in Row1?

  Jeff

  Jeffrey Williams
  “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

  ― Charles R. Swindoll

  How to post questions to get better answers faster
  Managing Transaction Logs

• Jeff Moden

  SSC Guru

  Points: 1004650

  More actions

  June 4, 2008 at 6:22 pm

  #824603

  kappa (6/4/2008)

  ---

  Question: HOW?

  Answer: CROSSTAB or PIVOT!

  ... but, like Jeffrey stated, you're gonna need another column of information from somewhere that maintains the sequencing of the rows. Of course, that information is in your other post.

  Speaking of "other post"... In your other post (not quite identical to this one) located at...

  http://www.sqlservercentral.com/Forums/Topic511296-8-1.aspx,

  ... I asked you a couple of important questions... head back there an gimme some answers so we can help... 😉

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• kappa

  SSC Veteran

  Points: 259

  More actions

  June 5, 2008 at 2:31 am

  #824673

  OK, heading over there right away...