Join or Transpose?

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • teneshap
    New Member
    • Oct 2009
    • 2

    #1

    Join or Transpose?

    I have a table that has the following fields:
    Patient/Date/Problem
    The problem field only has 3 different variables that can be used (which I'd like to make into fields).

    Example of how it looks:
    patient date problem
    1 9/1/01 HSF
    1 9/1/01 FTP
    1 1/1/2000 SDQ
    2 8/24/1999 HSF
    2 7/21/1998 SDQ


    What I am trying to do is make the table look like this:
    patient date HSF FTP SDQ
    1 9/1/01 Y Y Null
    2 8/24/1999 Y Null Null
    2 7/21/1998 Null Null Y


    I have tried the Pivot but I can't seem to get it to work. I thought I could just use a join, but apparently I am doing that wrong as well. Can you please assist me.

    Thank you,
    TMP
  • ck9663
    Recognized Expert Specialist
    • Jun 2007
    • 2878

    #2
    If PIVOT is not working, just use JOIN

    Code:
    select patient, date, 
    HSF = 
       case 
          when y1.problem = 'HSF' then 'Y
       else NULL end,
    FTP =
       case 
          when ftp.patient is not null then 'Y'
       else NULL end,
    SDQ =
       case 
          when sdq.patient is not null then 'Y'
       else NULL end
    from YourTable y1
    left join YourTable FTP on y1.patient = ftp.patient and problem = 'FTP'
    left join YourTable SDQ on y1.patient = sdq.patient and problem = 'SDQ'
    Happy Coding!!!


    --- CK

    Comment

    • teneshap
      New Member
      • Oct 2009
      • 2

      #3
      Okay, I think you meant ftp.problem and sdq.problem and so on. This is incredibly helpful with the case. I'm still confused with the Join.

      Once I use the Case
      select y1.patient, y1.date,
      HSF =
      case when y1.problem = 'hsf' then 'y'
      else null end,

      FTP =
      case when y1.problem = 'ftp' then 'y'
      else null end,

      SDQ =
      case when y1.problem = 'sdq' then 'y'
      else null end

      from #b y1
      I am able to create the new fields and the values are null where they should be. However, when I have a patient with the same ID and same date, and two different problems listed, I get two different rows. When I would like them to be on the same row. I would assume that is where the join comes in but I'm not exactly following it.

      Thanks so much for your help. You helped a lot already.

      Comment

      Working...