Join or Transpose?

[teneshap]

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]

If PIVOT is not working, just use JOIN

 
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

[teneshap]

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.