March 11, 2009 at 1:12 pm
I havent got a satisfactory solution to my prolbem
http://www.sqlservercentral.com/Forums/Topic672764-149-1.aspx
So im using a different approach.
I have created a table to take the records, but I need to know is there a way to do this as a s-proc from SQL.
psuedo code might look like
Start at record 1
get all records for sample N,
for each record of sample N
match test name with field
if match, then add record to appropriate field
next record
next sample
Tbl_Raw
Sample Test Result
100 Test1 +
100 Test2 -
100 Test3 -
101 Test1 +
101 Test2 -
tbl_compiled
Sample Test1 Test2 Test3
100 + - -
101 + -
March 11, 2009 at 2:07 pm
Are there a maximum of three tests? If so, this should do what you need:
CREATE TABLE #Table_3(
[Patient] [nchar](10) NULL,
[Sample] [int] NULL,
[Test] [nchar](10) NULL,
[Result] [nchar](10) NULL
)
Insert Into #Table_3 Select Patient='One ', Sample=100, Test='One ', Result='Positive '
Insert Into #Table_3 Select Patient='One ', Sample=100, Test='Two ', Result='Positive '
Insert Into #Table_3 Select Patient='One ', Sample=100, Test='Three ', Result='Negative '
Insert Into #Table_3 Select Patient='One ', Sample=101, Test='One ', Result='Positive '
Insert Into #Table_3 Select Patient='One ', Sample=101, Test='Two ', Result='Positive '
Insert Into #Table_3 Select Patient='Two ', Sample=102, Test='One ', Result='Negative '
Insert Into #Table_3 Select Patient='Two ', Sample=102, Test='Two ', Result='Negative '
Insert Into #Table_3 Select Patient='Two ', Sample=102, Test='Three ', Result='Positive '
;with
Test1
(Patient, Sample, Test1)
as
(select patient, sample, result
from #Table_3
where Test = 'One'),
Test2
(Patient, Sample, Test2)
as
(select patient, sample, result
from #Table_3
where Test = 'Two'),
Test3
(Patient, Sample, Test3)
as
(select patient, sample, result
from #Table_3
where Test = 'Three')
select
coalesce(Test1.Patient, Test2.Patient, Test3.Patient) as Patient,
coalesce(Test1.Sample, Test2.Sample, Test3.Sample) as Sample,
Test1,
Test2,
Test3
from Test1
full outer join Test2
on
Test1.Patient = Test2.Patient
and
Test1.Sample = Test2.Sample
full outer join Test3
on
Test1.Patient = Test3.Patient
and
Test1.Sample = Test3.Sample
or
Test2.Patient = Test3.Patient
and
Test2.Sample = Test3.Sample;
You'll need to modify it to use your real table instead of my temp table, but that should be easy enough.
Will that do what you need?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply