Author |
Topic |
slider
Starting Member
11 Posts |
Posted - 2005-06-07 : 14:19:55
|
i have 6 columns that have values between 0 and 1 let say these columns are a,b,c,d,e,f i have 6 other columns say p1,p2,p3,p4,p5,p6what i need is thisp1 = first column name that has value of 1 form the group a..fp2= second column name that hase value of 1 from the same groupand so on........so if a=1,b=1,c=1,d=0,e=0,f=1 then p1=a, p2=b, p3=c, p4=f who would i acomplish this using T-SQL ,, may be cursor?Any help is very welcome....... thanks |
|
X002548
Not Just a Number
15586 Posts |
|
slider
Starting Member
11 Posts |
Posted - 2005-06-07 : 14:32:59
|
well my question was generic ,, just to make it simple but i have 6 columns whose names corresponds to some patient reported diagnosis -- so if isTB=1 then patient has reported TB ,,i want to change that to new number coding system i.e. p1 to p6 will hold code for the diagnosis,, but new columns are not one-to-one match but only populate if there is data in those columns based on the order from a to f ....order is important and its based on value of the diagnosis ,, hope it helps |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-06-07 : 14:45:20
|
There's got to be a better way to organize the data. Have you considered creating a set of new columns P1...P6 and populating them with diagnosis information?[CODE]SET P1 = CASE WHEN A = 1 THEN A WHEN B = 1 THEN B -- Oh! This hurts... WHEN C = 1 THEN C -- Argh! WHEN D = 1 THEN D -- Someone shoot me... WHEN E = 1 THEN E -- --- * --- WHEN F = 1 THEN F ELSE NULLEND[/CODE] |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-06-07 : 14:53:11
|
Read up on normalization:http://www.datamodel.org/NormalizationRules.htmlIf you have a table of Patients, with a primary key of PatientID, and each patient can have multiple diagnosis, then should should break those out into a related table:PatientDiagonisis (PatientID, DiagnosisID, Date, Notes, ... etc ...)with a primary key of PatientID/DiagnosisID. You would then have a master table listing all possible values for Diagnosis.- Jeff |
|
|
slider
Starting Member
11 Posts |
Posted - 2005-06-07 : 14:53:18
|
this wont work :( lets say A=1 and C=1SET P1 = CASE WHEN A = 1 THEN A WHEN B = 1 THEN B -- Oh! This hurts... WHEN C = 1 THEN C -- Argh! WHEN D = 1 THEN D -- Someone shoot me... WHEN E = 1 THEN E -- --- * --- WHEN F = 1 THEN F ELSE NULLENDSO P1= ASET P2 = CASE WHEN A = 1 THEN A WHEN B = 1 THEN B -- Oh! This hurts... WHEN C = 1 THEN C -- Argh! WHEN D = 1 THEN D -- Someone shoot me... WHEN E = 1 THEN E -- --- * --- WHEN F = 1 THEN F ELSE NULLENDso P2=A but if u think about it doing it this way it will get clear that it wont work.. once u have P1= a..f , P2 should only look down the group and skip P1 value so if A=1, B=1,C=0, D=1 ...P1=AP2 should skip colum in P1 ,, so P2=BP3=D after sking A,B and C=0thanks |
|
|
slider
Starting Member
11 Posts |
Posted - 2005-06-07 : 14:56:39
|
quote: Originally posted by jsmith8858 PatientDiagonisis (PatientID, DiagnosisID, Date, Notes, ... etc ...)with a primary key of PatientID/DiagnosisID- Jeff
breaking the table is not an option for this ,,,,thanks |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-06-07 : 15:26:36
|
>>breaking the table is not an option for this ,,,,thanksI think Jeff was suggesting that you fix the table, not break it :)--You should have provided something like this as DDL/DMLset nocount ondeclare @tb table (a tinyint, b tinyint, c tinyint, d tinyint, e tinyint, f tinyint, p1 char(1), p2 char(1), p3 char(1), p4 char(1), p5 char(1), p6 char(1))insert @tb (a,b,c,d,e,f)select 1,1,1,0,0,1 union allselect 1,1,0,0,0,1 union allselect 0,0,0,0,1,0 union allselect 0,0,0,1,0,1--Here is one way to do it (if I understand you correctly)update a set p1 = case when a = 1 then 'a' when b = 1 then 'b' when c = 1 then 'c' when d = 1 then 'd' when e = 1 then 'e' when f = 1 then 'f' end ,p2 = case when a+b = 2 then 'b' when a+b+c = 2 then 'c' when a+b+c+d = 2 then 'd' when a+b+c+d+e = 2 then 'e' when a+b+c+d+e+f = 2 then 'f' end ,p3 = case when a+b+c = 3 then 'c' when a+b+c+d = 3 then 'd' when a+b+c+d+e = 3 then 'e' when a+b+c+d+e+f = 3 then 'f' end ,p4 = case when a+b+c+d = 4 then 'd' when a+b+c+d+e = 4 then 'e' when a+b+c+d+e+f = 4 then 'f' end ,p5 = case when a+b+c+d+e = 5 then 'e' when a+b+c+d+e+f = 5 then 'f' end ,p6 = case when a+b+c+d+e+f = 6 then 'f' endfrom @tb a--show resultsselect * from @tb Be One with the OptimizerTG |
|
|
slider
Starting Member
11 Posts |
Posted - 2005-06-07 : 16:19:58
|
TG many thanks,, i think this should work just fine ,, seems clever way to do this,, thanks again,, |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-06-07 : 16:49:45
|
quote: Originally posted by slider
quote: Originally posted by jsmith8858 PatientDiagonisis (PatientID, DiagnosisID, Date, Notes, ... etc ...)with a primary key of PatientID/DiagnosisID- Jeff
breaking the table is not an option for this ,,,,thanks
Is fixing the table an option?Apparently, you didn't visit the link I provided either ....- Jeff |
|
|
|
|
|