CODEWITHSUNDEEP
sqlsql-server
Perry
Perry

Reputation: 1337

SQL Combine duplicate columns to 1

I have this query

select im_Call,IM_DR
from cadw.tiburon.inmast m
where IM_DATE < Convert(date,GETDATE()) AND IM_DATE >= DATEADD(DD, - 4,Convert(date,GETDATE()))

which returns results similar to this

enter image description here

I need to take the records that are highlited in yellow so I get results that would return 1 row with the im_Call number with the IM_DR column showing both of the highlighted records as 1 row with 2 columns like this

im_Call     IM_DR
163220023   160104238, 160104238

Upvotes: 1

Views: 267

Answers (2)

Shakeer Mirza
Shakeer Mirza

Reputation: 5110

You need to do trick with For xml path and Stuff function.

 ;with cte as (

   select im_Call,IM_DR
   from cadw.tiburon.inmast m
   where IM_DATE < Convert(date,GETDATE()) AND
    IM_DATE >=   
    DATEADD(DD,-4,Convert(date,GETDATE()))

  )
  Select C1.im_Call , (Select stuff((select
    distinct  ','+cast(C2.IM_DR as varchar(20)) from cte C2 
  where c2.Im_call= C1.Im_call for xml path('') ),1,1,''))
  as  IM_DR
  from CTE C1

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 81970

Perhaps this can help

Edit- Added the CTE to keep the scope of the original query

 ;with cte as (
    Select im_Call,IM_DR
    from cadw.tiburon.inmast m
    where IM_DATE < Convert(date,GETDATE()) AND IM_DATE >= DATEADD(DD, - 4,Convert(date,GETDATE()))
) 
 Select IM_Call
       ,IM_DR = (Select Stuff((Select Distinct ',' +cast(IM_DR as varchar(25)) From cte Where IM_Call=A.IM_Call For XML Path ('')),1,1,'') )
 From (Select Distinct IM_Call From cte) A

Upvotes: 1

Related Questions