Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 i am new and need some help here!

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,p6

what i need is this

p1 = first column name that has value of 1 form the group a..f
p2= second column name that hase value of 1 from the same group
and 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

Posted - 2005-06-07 : 14:24:28
I gotta ask.

What's the real need to do this?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

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
Go to Top of Page

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 NULL
END[/CODE]
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-06-07 : 14:53:11
Read up on normalization:

http://www.datamodel.org/NormalizationRules.html

If 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
Go to Top of Page

slider
Starting Member

11 Posts

Posted - 2005-06-07 : 14:53:18
this wont work :( lets say A=1 and C=1

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 NULL
END

SO P1= A


SET 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 NULL
END

so 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=A

P2 should skip colum in P1 ,, so P2=B

P3=D after sking A,B and C=0

thanks


Go to Top of Page

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
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-06-07 : 15:26:36
>>breaking the table is not an option for this ,,,,thanks
I think Jeff was suggesting that you fix the table, not break it :)


--You should have provided something like this as DDL/DML
set nocount on
declare @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 all
select 1,1,0,0,0,1 union all
select 0,0,0,0,1,0 union all
select 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'
end
from @tb a

--show results
select * from @tb


Be One with the Optimizer
TG
Go to Top of Page

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,,
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -