By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,415 Members | 1,568 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,415 IT Pros & Developers. It's quick & easy.

Join or Transpose?

P: 2
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
Oct 7 '09 #1
Share this Question
Share on Google+
2 Replies


ck9663
Expert 2.5K+
P: 2,878
If PIVOT is not working, just use JOIN

Expand|Select|Wrap|Line Numbers
  1.  
  2. select patient, date, 
  3. HSF = 
  4.    case 
  5.       when y1.problem = 'HSF' then 'Y
  6.    else NULL end,
  7. FTP =
  8.    case 
  9.       when ftp.patient is not null then 'Y'
  10.    else NULL end,
  11. SDQ =
  12.    case 
  13.       when sdq.patient is not null then 'Y'
  14.    else NULL end
  15. from YourTable y1
  16. left join YourTable FTP on y1.patient = ftp.patient and problem = 'FTP'
  17. left join YourTable SDQ on y1.patient = sdq.patient and problem = 'SDQ'
  18.  
  19.  
Happy Coding!!!


--- CK
Oct 7 '09 #2

P: 2
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.
Oct 11 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.