SQLTeam.com | Weblogs | Forums

Need to combine multiple rows that have varying column values into one row


#1

I have a query that is producing the following output:

TX00261394201 2016-05-17 0 0 0 0 0 0 0 1 0 0 0 0
TX00261394201 2016-05-17 0 0 1 0 0 0 0 0 0 0 0 0

I need it to appear like this:

TX00261394201 2016-05-17 0 0 1 0 0 0 0 1 0 0 0 0

I have been struggling for days on this can someone help?


#2

Would have to see the original query in order to modify it. In particular, don't know if you are using a GROUP BY already in that query or not.


#3

Here is my final select query. I am running several subqueries already to populate some temp tables. Is this enough to go on?

select
max(Comp.external_ID) external_ID
,max(Comp.member_name) member_name

,calls.date_called
,calls.hung_up
,calls.Appt_Sched
,calls.Refused
,calls.for_lang
,calls.Prior_Exam
,calls.Sched_Appt
,calls.Termed
,calls.No_Answer
,calls.No_Phone
,calls.Schedule
,calls.Wrong_Number
,calls.LTC

from
#compliance comp
left outer join #calls calls on Comp.external_ID = calls.amysisnbr

group by

 Comp.external_ID
,Comp.member_name 
,comp.Plan_PrimaryIdentifier
,Comp.plan_Secondaryidentifier
,comp.Compliance_Indicator
,Comp.service_date
,Comp.claim_number
,calls.Dob 
,calls.Plan_Code
,calls.Date_Called 
,calls.hung_up
,calls.Appt_Sched
,calls.Refused
,calls.for_lang
,calls.Prior_Exam
,calls.Sched_Appt
,calls.Termed
,calls.No_Answer
,calls.No_Phone
,calls.Schedule
,calls.Wrong_Number
,calls.LTC

order by
comp.member_name


#4

If you need the entire query, I can post that too.


#5

I have obviously not included some of the columns in the output for security reasons.


#6

My best is to try something more like this:

select
Comp.external_ID external_ID
,Comp.member_name member_name
,calls.date_called
,calls.hung_up
,calls.Appt_Sched
,calls.Refused
,calls.for_lang
,calls.Prior_Exam
,calls.Sched_Appt
,calls.Termed
,calls.No_Answer
,calls.No_Phone
,calls.Schedule
,calls.Wrong_Number
,calls.LTC
from #compliance comp
left outer join (
    select amysisnbr
        ,sum(date_called) as date_called
        ,sum(hung_up) as hung_up
        ,sum(Appt_Sched) as appt_sched
        ,sum(Refused) as refused
        ,sum(for_lang) as for_lang
        ,sum(Prior_Exam) as prior_exam
        ,sum(Sched_Appt) as sched_appt
        ,sum(Termed) as termed
        ,sum(No_Answer) as no_answer
        ,sum(No_Phone) as no_phone
        ,sum(Schedule) as schedule
        ,sum(Wrong_Number) as wrong_number
        ,sum(LTC) as LTC
    from #calls
    group by amysisnbr
) as calls on Comp.external_ID = calls.amysisnbr