Self join solution
-
I have a tsql puzzler looking for some direction on best approach
here is my current sql
select distinct a.patientid,a.allergenname ,rr.name as reaction
from allergy a
left outer join allergyreaction r
on r.allergyid = a.id
inner join reaction rr on rr.id = r.reactionid
where sendtopharmacy = 'true'
order by a.patientid,a.allergenname
and here are the results
88Avar CleanserAnemia
88Avar CleanserWheezing
88Avena SativaOther
88Avena SativaRash
88Avena SativaShock
224Loosen Nausea
What I need is a record with patientid, allergy and then all the reactions as one row. There could be from 1-6 reactions.
-
this may help you.
-
Assuming you really only have 6 reactions, a crosstab will work:
[font="Courier New"]CREATE TABLE #Reaction (ReactionID INT, Description VARCHAR(20))
INSERT #Reaction VALUES (1,'Amnesia')
INSERT #Reaction VALUES (2,'Wheezing')
INSERT #Reaction VALUES (3,'Rash')
INSERT #Reaction VALUES (4,'Fatigue')
INSERT #Reaction VALUES (5,'Shock')
INSERT #Reaction VALUES (6,'Nausea')
CREATE TABLE #Allergy (AllergyID INT, Description VARCHAR(20))
INSERT #Allergy VALUES (1,'Avar Cleanser')
INSERT #Allergy VALUES (2,'Avena Sativa')
CREATE TABLE #AllergyReaction (AllergyID INT, ReactionID INT)
INSERT #AllergyReaction VALUES (1,1)
INSERT #AllergyReaction VALUES (1,2)
INSERT #AllergyReaction VALUES (1,3)
INSERT #AllergyReaction VALUES (2,3)
INSERT #AllergyReaction VALUES (2,4)
SELECT
A.*
, (SELECT R.Description FROM #Reaction R INNER JOIN #AllergyReaction AR ON R.ReactionID = AR.ReactionID WHERE AR.AllergyID = A.AllergyID AND AR.ReactionID = 1) AS Reaction1
, (SELECT R.Description FROM #Reaction R INNER JOIN #AllergyReaction AR ON R.ReactionID = AR.ReactionID WHERE AR.AllergyID = A.AllergyID AND AR.ReactionID = 2) AS Reaction2
, (SELECT R.Description FROM #Reaction R INNER JOIN #AllergyReaction AR ON R.ReactionID = AR.ReactionID WHERE AR.AllergyID = A.AllergyID AND AR.ReactionID = 3) AS Reaction3
, (SELECT R.Description FROM #Reaction R INNER JOIN #AllergyReaction AR ON R.ReactionID = AR.ReactionID WHERE AR.AllergyID = A.AllergyID AND AR.ReactionID = 4) AS Reaction4
, (SELECT R.Description FROM #Reaction R INNER JOIN #AllergyReaction AR ON R.ReactionID = AR.ReactionID WHERE AR.AllergyID = A.AllergyID AND AR.ReactionID = 5) AS Reaction5
, (SELECT R.Description FROM #Reaction R INNER JOIN #AllergyReaction AR ON R.ReactionID = AR.ReactionID WHERE AR.AllergyID = A.AllergyID AND AR.ReactionID = 6) AS Reaction6
FROM
#Allergy A[/font]
-
Michael Earl (12/10/2008)
Assuming you really only have 6 reactions, a crosstab will work:[font="Courier New"]CREATE TABLE #Reaction (ReactionID INT, Description VARCHAR(20))
INSERT #Reaction VALUES (1,'Amnesia')
INSERT #Reaction VALUES (2,'Wheezing')
INSERT #Reaction VALUES (3,'Rash')
INSERT #Reaction VALUES (4,'Fatigue')
INSERT #Reaction VALUES (5,'Shock')
INSERT #Reaction VALUES (6,'Nausea')
CREATE TABLE #Allergy (AllergyID INT, Description VARCHAR(20))
INSERT #Allergy VALUES (1,'Avar Cleanser')
INSERT #Allergy VALUES (2,'Avena Sativa')
CREATE TABLE #AllergyReaction (AllergyID INT, ReactionID INT)
INSERT #AllergyReaction VALUES (1,1)
INSERT #AllergyReaction VALUES (1,2)
INSERT #AllergyReaction VALUES (1,3)
INSERT #AllergyReaction VALUES (2,3)
INSERT #AllergyReaction VALUES (2,4)
SELECT
A.*
, (SELECT R.Description FROM #Reaction R INNER JOIN #AllergyReaction AR ON R.ReactionID = AR.ReactionID WHERE AR.AllergyID = A.AllergyID AND AR.ReactionID = 1) AS Reaction1
, (SELECT R.Description FROM #Reaction R INNER JOIN #AllergyReaction AR ON R.ReactionID = AR.ReactionID WHERE AR.AllergyID = A.AllergyID AND AR.ReactionID = 2) AS Reaction2
, (SELECT R.Description FROM #Reaction R INNER JOIN #AllergyReaction AR ON R.ReactionID = AR.ReactionID WHERE AR.AllergyID = A.AllergyID AND AR.ReactionID = 3) AS Reaction3
, (SELECT R.Description FROM #Reaction R INNER JOIN #AllergyReaction AR ON R.ReactionID = AR.ReactionID WHERE AR.AllergyID = A.AllergyID AND AR.ReactionID = 4) AS Reaction4
, (SELECT R.Description FROM #Reaction R INNER JOIN #AllergyReaction AR ON R.ReactionID = AR.ReactionID WHERE AR.AllergyID = A.AllergyID AND AR.ReactionID = 5) AS Reaction5
, (SELECT R.Description FROM #Reaction R INNER JOIN #AllergyReaction AR ON R.ReactionID = AR.ReactionID WHERE AR.AllergyID = A.AllergyID AND AR.ReactionID = 6) AS Reaction6
FROM
#Allergy A[/font]
Another way avoiding subqueries
SELECT A.AllergyID,A.Description,MAX(CASE WHEN AR.ReactionID = 1 THEN R.Description END) AS Reaction1,
MAX(CASE WHEN AR.ReactionID = 2 THEN R.Description END) AS Reaction2,
MAX(CASE WHEN AR.ReactionID = 3 THEN R.Description END) AS Reaction3,
MAX(CASE WHEN AR.ReactionID = 4 THEN R.Description END) AS Reaction4,
MAX(CASE WHEN AR.ReactionID = 5 THEN R.Description END) AS Reaction5,
MAX(CASE WHEN AR.ReactionID = 6 THEN R.Description END) AS Reaction6
FROM #Allergy A
LEFT OUTER JOIN #AllergyReaction AR ON AR.AllergyID = A.AllergyID
LEFT OUTER JOIN #Reaction R ON R.ReactionID = AR.ReactionID
GROUP BY A.AllergyID,A.Description
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
timscronin , you can use PIVOT clause for this solution if you are on SQL Server 2005 as follows. I notice that your table allergy has a column patientid but not so in the solutions posted by Michael Earl and Mark.
You should provide the script for the structure of the tables and sample data for better response and clarity of your requirement.
select patientid, allergenname, (case([1]) when '1' then 'Anemia' end) as Anemia,(case([2]) when '2' then 'Yes' else 'No' end) as Wheezing,
(case([3]) when '3' then 'Yes' else 'No' end) as Other,
(case([4]) when '4' then 'Yes' else 'No' end) as Rash,
(case([5]) when '5' then 'Yes' else 'No' end) as Shock,
(case([6]) when '6' then 'Yes' else 'No' end) as Nausea
from
(select distinct a.patientid,a.allergenname ,
(case rr.name when Anemia then 1
when Wheezing then 2
when Other then 3
when Rash then 4
when Shock then 5
when Nausea then 6
end) as reaction
from #Allergy a
left outer join #allergyreaction r
on r.allergyid = a.AllergyID
inner join #Reaction rr on rr.ReactionID = r.reactionid) x
PIVOT (SUM(reaction)FOR reaction IN ([1],[2],[3],[4],[5],[6])
) AS PVT
The result should be as follows:
patientid allergenname Anemia Wheezing Other Rash Shock Nausea
88 Avar Cleanser Yes Yes Yes Yes Yes No
224 Loosen No No No No No Yes
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply