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.

    http://technet.microsoft.com/en-us/library/ms182741.aspx

  • 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