SQL grouping together so data does not repeat

Question

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

Please do not post [images](https://meta.stackoverflow.com/questions/285551) of code or data. No one can copy/paste data from a picture. As explained in the [question guide](https://stackoverflow.com/help/how-to-ask), Sample data should be *consumable text* in your question, ideally as *create* and *insert* statements, or alternatively a [DB<>Fiddle](https://dbfiddle.uk/). — Stu, May 17 '22 at 21:49
There is no repeating data in your result, each row is unique. Please explain, by providing sample data and the result you want to achieve as editable text, what you are trying to accomplish — NickW, May 17 '22 at 22:21
your image is not showing. So please write down what you wanted to show — Shu Rahman, May 18 '22 at 10:03
@stu thank you for your advice. i hope the edits i made to this question will help and someone will be able to help me out. — Lern2LveWithIt, May 18 '22 at 17:24
@NickW thank you for your advice. i hope the edits i made to this question will help and someone will be able to help me out. — Lern2LveWithIt, May 18 '22 at 17:25

score 1 · Accepted Answer · answered May 18 '22 at 18:16

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:

See Demo Fiddle

SQL grouping together so data does not repeat

1 Answers1