Transpose data?

• kwaldman 36287

  Newbie

  Points: 9

  More actions

  April 7, 2014 at 12:24 pm

  #290016

  Hi all,

  I'm new to SQL and these forums, but I'd love some help! I have a table that has data on drugs that people receive during hospital stays. Each person has multiple rows if they have multiple days in the hospital, and each drug has its own row.

  For example,

  PatientID Date DrugName

  1 1/1/14 Drug1

  1 1/1/14 Drug2

  1 1/1/14 Drug3

  2 1/3/14 Drug1

  2 1/4/14 Drug2

  3 1/6/14 Drug1

  3 1/6/14 Drug2

  3 1/6/14 Drug3

  I would like to transform the data so that it looks like this:

  PatientID Date Drug1 Drug2 Drug3

  1 1/1/14 1 1 1

  2 1/3/14 1 0 0

  2 1/4/14 0 1 0

  3 1/6/14 1 1 1

  I'm totally stuck trying to figure out what seems like it should be very simple, any help is appreciated!

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  April 7, 2014 at 12:31 pm

  #1703917

  this may help you on your way

  http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• Jeff Moden

  SSC Guru

  Points: 1003863

  More actions

  April 7, 2014 at 1:30 pm

  #1703933

  kwaldman 36287 (4/7/2014)

  ---

  Hi all,

  I'm new to SQL and these forums, but I'd love some help! I have a table that has data on drugs that people receive during hospital stays. Each person has multiple rows if they have multiple days in the hospital, and each drug has its own row.

  For example,

  PatientID Date DrugName

  1 1/1/14 Drug1

  1 1/1/14 Drug2

  1 1/1/14 Drug3

  2 1/3/14 Drug1

  2 1/4/14 Drug2

  3 1/6/14 Drug1

  3 1/6/14 Drug2

  3 1/6/14 Drug3

  I would like to transform the data so that it looks like this:

  PatientID Date Drug1 Drug2 Drug3

  1 1/1/14 1 1 1

  2 1/3/14 1 0 0

  2 1/4/14 0 1 0

  3 1/6/14 1 1 1

  I'm totally stuck trying to figure out what seems like it should be very simple, any help is appreciated!

  See the following 'cuz I just know you're going to need dynamic SQL to do this... 😉

  http://www.sqlservercentral.com/articles/Crosstab/65048/

  --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)