SQL Server SQL grouping together so data does not repeat

f4t66c6m 于 12个月前发布在其他

关注(0)|答案(1)|浏览(107)

Have data in a table, and trying to query it out so the data reads for example like Motrin | mg | 25 | 4 | day | Pain, in one row. from the image...i have them all under the Answer column. My query that i have brings them all out...but it repeats the data. Can anyone help with grouping this under the DrilldownQuestionID?

Below is the sample data i created:
| PracticeID | PatientID | ParentPageID | ParentPageNumber | QuestionID | DrilldownQuestionID | DrilldownAnsRecordID | EncounterID | Answer |
| ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ |
| 1 | 111111 | 48 | 5 | 1475 | 2091 | 1 | encounter1 | Tylenol |
| 1 | 111111 | 48 | 5 | 1475 | 2092 | 1 | encounter1 | mg |
| 1 | 111111 | 48 | 5 | 1475 | 2093 | 1 | encounter1 | 500 |
| 1 | 111111 | 48 | 5 | 1475 | 2094 | 1 | encounter1 | 3 |
| 1 | 111111 | 48 | 5 | 1475 | 2095 | 1 | encounter1 | day |
| 1 | 111111 | 48 | 5 | 1475 | 2096 | 1 | encounter1 | Headache |
| 1 | 111111 | 48 | 5 | 1475 | 2091 | 2 | encounter1 | Motrin |
| 1 | 111111 | 48 | 5 | 1475 | 2092 | 2 | encounter1 | mg |
| 1 | 111111 | 48 | 5 | 1475 | 2093 | 2 | encounter1 | 25 |
| 1 | 111111 | 48 | 5 | 1475 | 2094 | 2 | encounter1 | 4 |
| 1 | 111111 | 48 | 5 | 1475 | 2095 | 2 | encounter1 | day |
| 1 | 111111 | 48 | 5 | 1475 | 2096 | 2 | encounter1 | Pain |

my query below is what I have;

select distinct j.Answer as Name, 
    n.Answer as Dosage,
    k.Answer as [Dosage Unit], 
    o.Answer as [How Many],
    l.Answer as [How Often], 
    m.Answer as [Taken For]
from
    (select isnull(a.[Answer],'') as Answer, 
        a.EncounterID  
    from C_ItemDrillDown c join D_AWVAnswers a 
        on c.ItemDrilldownID = a.DrilldownQuestionID 
        and a.QuestionID = 1475 
        and a.DrilldownQuestionID = 2091
    where a.EncounterID = @EncounterID) as j
        join
    (select isnull(a.[Answer],'') as Answer, 
        a.EncounterID  
    from C_ItemDrillDown c join D_AWVAnswers a 
        on c.ItemDrilldownID = a.DrilldownQuestionID 
        and a.QuestionID = 1475 
        and a.DrilldownQuestionID = 2093
    where a.EncounterID = @EncounterID) as n
        on j.EncounterID = n.EncounterID
        join
    (select isnull(a.[Answer],'') as Answer, 
        a.EncounterID 
    from C_ItemDrillDown c join D_AWVAnswers a 
        on c.ItemDrilldownID = a.DrilldownQuestionID 
        and a.QuestionID = 1475 
        and a.DrilldownQuestionID = 2092
    where a.EncounterID = @EncounterID) as k
        on n.encounterID = k.EncounterID
        join
    (select isnull(a.[Answer],'') as Answer, 
        a.EncounterID  
    from C_ItemDrillDown c join D_AWVAnswers a 
        on c.ItemDrilldownID = a.DrilldownQuestionID 
        and a.QuestionID = 1475 
        and a.DrilldownQuestionID = 2094
    where a.EncounterID = @EncounterID) as o
        on k.EncounterID = o.EncounterID
        join
    (select isnull(a.[Answer],'') as Answer, 
        a.EncounterID 
    from C_ItemDrillDown c join D_AWVAnswers a 
        on c.ItemDrilldownID = a.DrilldownQuestionID 
        and a.QuestionID = 1475 
        and a.DrilldownQuestionID = 2095
    where a.EncounterID = @EncounterID) as l
        on o.encounterID = l.EncounterID
        join
    (select isnull(a.[Answer],'') as Answer, 
        a.EncounterID 
    from C_ItemDrillDown c join D_AWVAnswers a 
        on c.ItemDrilldownID = a.DrilldownQuestionID 
        and a.QuestionID = 1475 
        and a.DrilldownQuestionID = 2096
    where a.EncounterID = @EncounterID) as m
        on l.encounterID = m.EncounterID

What i would like as a result is:

Name	Dosage	Dosage Unit	How Many	How Often	Taken For
Tylenol	500	mg	3	day	Headache
Motrin	25	mg	4	day	Pain

sql-server

来源：https://stackoverflow.com/questions/72280934/sql-grouping-together-so-data-does-not-repeat

1条答案

按热度按时间

nfs0ujit1#

So it looks like you're after straight-forward pivot, something like the following:

select 
  max(case when DrilldownQuestionID = 2091 then Answer end) [Name],
  max(case when DrilldownQuestionID = 2092 then Answer end) [Dosage Unit],
  max(case when DrilldownQuestionID = 2093 then Answer end) Dosage,
  max(case when DrilldownQuestionID = 2094 then Answer end) [How Many],
  max(case when DrilldownQuestionID = 2095 then Answer end) [How Often],
  max(case when DrilldownQuestionID = 2096 then Answer end) [Taken For]
from t
group by DrilldownAnsRecordID;

I've omitted the redundant (to the problem) columns which you would also group-by if you needed to use them.

Result: