[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Can I combine column values for a specific ID in SQL?

Posted on 2009-12-22
25
Medium Priority
?
269 Views
Last Modified: 2012-05-08
I have the attached SQL query. It returns the correct data but in some cases the occ.detail column may contain either an email address or a phone number or both for a certain unique id. Due to this, when I use  'distinct' on this column more than one row is returned for the same cpo.firstname and cpo.surname.

Is there a way that I can combine these different values returned for my occ.detail column so they are both show within just the one row instead of two or more? I'm trying to avoid showing duplicate cpo.firstname and cpo.surname values on my frontend.

Thanks.
select distinct cpo.firstname, cpo.surname, occ.detail
from tblCrmPersonObjects cpo
inner join tblCrmContactObjectLinks ccol
on cpo.crm_person_object_id = ccol.crm_object_id
inner join tblCrmContactObjects occ
on ccol.crm_contact_object_id = occ.crm_contact_object_id

Open in new window

0
Comment
Question by:MartinPetex
  • 10
  • 7
  • 4
  • +1
25 Comments
 
LVL 14

Expert Comment

by:shru_0409
ID: 26104716

select distinct cpo.firstname, cpo.surname, occ.detail ,CONCAT(cpo.firstname,cpo.surname) name
from tblCrmPersonObjects cpo
inner join tblCrmContactObjectLinks ccol
on cpo.crm_person_object_id = ccol.crm_object_id
inner join tblCrmContactObjects occ
on ccol.crm_contact_object_id = occ.crm_contact_object_id

try this
0
 
LVL 14

Expert Comment

by:shru_0409
ID: 26104726
in sql server try this...
select distinct cpo.firstname, cpo.surname, occ.detail ,
cpo.firstname + ' ' + cpo.surname nm
from tblCrmPersonObjects cpo
inner join tblCrmContactObjectLinks ccol
on cpo.crm_person_object_id = ccol.crm_object_id
inner join tblCrmContactObjects occ
on ccol.crm_contact_object_id = occ.crm_contact_object_id
0
 

Author Comment

by:MartinPetex
ID: 26104894
shru,

Your first suggestion threw the error: "CONCAT is not recognised" and your second suggestion returned the same results as before but with a new column (nm) containing the full name (cpo.firstname, cpo.surname). Sorry if I wasn't clear, but what I'm hoping to achieve is merge different values returned for my occ.detail column for every unique cpo.firstname, cpo.surname, avoiding duplicate entries. Possible?

Thanks.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 14

Expert Comment

by:shru_0409
ID: 26105032
u want to avoid duplicate rows which is first name and last name..

try this

select z.firstname, z.surname, z.detail from ( select cpo.firstname, cpo.surname, occ.detail, row_number() over ( partition by cpo.firstname, cpo.surname order by   cpo.firstname, cpo.surname ) row_num
from tblCrmPersonObjects cpo
inner join tblCrmContactObjectLinks ccol
on cpo.crm_person_object_id = ccol.crm_object_id
inner join tblCrmContactObjects occ
on ccol.crm_contact_object_id = occ.crm_contact_object_id) z
where z.row_num= 1


0
 

Author Comment

by:MartinPetex
ID: 26105128
shru,

That worked in regards to removing the duplicate rows for cpo.firstname and cpo.surname but the second occ.detail value (email address) for a certain cpo.firstname and cpo.surname was not returned, the first value (phone number) was returned but not the second value?

Thanks.
0
 
LVL 41

Accepted Solution

by:
Sharath earned 1400 total points
ID: 26105145
try this
;with CTE as (
select distinct cpo.firstname, cpo.surname, occ.detail
from tblCrmPersonObjects cpo
inner join tblCrmContactObjectLinks ccol
on cpo.crm_person_object_id = ccol.crm_object_id
inner join tblCrmContactObjects occ
on ccol.crm_contact_object_id = occ.crm_contact_object_id)

select distinct firstname,surname,
       rtrim(substring(isnull((select ',' + detail from CTE t1 
                                where t1.firstname = t2.firstname and t1.surname = t2.surname for xml path('')),' '),2,2000))
from CTE t2

Open in new window

0
 
LVL 41

Expert Comment

by:ralmada
ID: 26105146
Give this a try:
select distinct 
         cpo.firstname, 
	cpo.surname, 
	details = stuff((select distinct ',' + occ.detail 
			from tblCrmContactObjects occ 
			where ccol.crm_contact_object_id = occ.crm_contact_object_id
			for xml path('') order by 1), 1, 1, '')
from tblCrmPersonObjects cpo 
inner join tblCrmContactObjectLinks ccol on cpo.crm_person_object_id = ccol.crm_object_id

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 26105156
If you are getting any conversion errors, they try this
;with CTE as (
select distinct cpo.firstname, cpo.surname, convert(nvarchar,occ.detail) as detail
from tblCrmPersonObjects cpo
inner join tblCrmContactObjectLinks ccol
on cpo.crm_person_object_id = ccol.crm_object_id
inner join tblCrmContactObjects occ
on ccol.crm_contact_object_id = occ.crm_contact_object_id)

select distinct firstname,surname,
       rtrim(substring(isnull((select ',' + detail from CTE t1 
                                where t1.firstname = t2.firstname and t1.surname = t2.surname for xml path('')),' '),2,2000))
from CTE t2

Open in new window

0
 
LVL 41

Expert Comment

by:ralmada
ID: 26105169
don't think you will get any convertion error, but just in case:
select distinct  
         cpo.firstname,  
        cpo.surname,  
        details = stuff((select distinct ',' + cast(occ.detail as varchar)
                        from tblCrmContactObjects occ  
                        where ccol.crm_contact_object_id = occ.crm_contact_object_id 
                        for xml path('') order by 1), 1, 1, '') 
from tblCrmPersonObjects cpo  
inner join tblCrmContactObjectLinks ccol on cpo.crm_person_object_id = ccol.crm_object_id

Open in new window

0
 
LVL 14

Expert Comment

by:shru_0409
ID: 26105193
select z.firstname, z.surname, z.detail from ( select cpo.firstname, cpo.surname, occ.detail, row_number() over ( partition by cpo.firstname, cpo.surname order by   cpo.firstname, cpo.surname ) row_num
from tblCrmPersonObjects cpo
inner join tblCrmContactObjectLinks ccol
on cpo.crm_person_object_id = ccol.crm_object_id
right outer join tblCrmContactObjects occ
on ccol.crm_contact_object_id = occ.crm_contact_object_id) z
where z.row_num= 1

--- use the outer join of occ table
0
 

Author Comment

by:MartinPetex
ID: 26105205
ralmada,

From your suggestion I get the error: "incorrect syntax near order"?

Thanks.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 26105223
Well you can remove it
select distinct   
         cpo.firstname,   
        cpo.surname,   
        details = stuff((select distinct ',' + cast(occ.detail as varchar) 
                        from tblCrmContactObjects occ   
                        where ccol.crm_contact_object_id = occ.crm_contact_object_id  
                        for xml path('')), 1, 1, '')  
from tblCrmPersonObjects cpo   
inner join tblCrmContactObjectLinks ccol on cpo.crm_person_object_id = ccol.crm_object_id

Open in new window

0
 
LVL 41

Expert Comment

by:ralmada
ID: 26105244
And actually the order by was misplaced
select distinct    
         cpo.firstname,    
        cpo.surname,    
        details = stuff((select distinct ',' + cast(occ.detail as varchar)  
                        from tblCrmContactObjects occ    
                        where ccol.crm_contact_object_id = occ.crm_contact_object_id   
                        order by 1
                        for xml path('')), 1, 1, '')   
from tblCrmPersonObjects cpo    
inner join tblCrmContactObjectLinks ccol on cpo.crm_person_object_id = ccol.crm_object_id

Open in new window

0
 

Author Comment

by:MartinPetex
ID: 26105274
Sharath,

That worked perfectly, thanks very much!! Just a shot in the dark but could you produce that query in LINQ??
0
 

Author Comment

by:MartinPetex
ID: 26105306
ralmada,

Your query seemed to produce the same results as my original query, i.e. duplicate firstname and surname values containing different detail values.
0
 

Author Comment

by:MartinPetex
ID: 26105339
shru,

again your query seemed to ignore my second value?

sharath's suggestion worked perfectly, but I now need to produce a linq version of his query if this is possible?
0
 
LVL 41

Assisted Solution

by:ralmada
ralmada earned 600 total points
ID: 26105357
Well, then you also have duplicates in contact link.
select distinct     
         cpo.firstname,     
        cpo.surname,     
        details = stuff((select distinct ',' + cast(occ.detail as varchar)   
                        from tblCrmContactObjects occ     
                       inner join tblCrmContactObjectLinks ccol on ccol.crm_contact_object_id = occ.crm_contact_object_id
where cpo.crm_person_object_id = ccol.crm_object_id
                        order by 1 
                        for xml path('')), 1, 1, '')    
from tblCrmPersonObjects cpo

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 26105358
>> Just a shot in the dark but could you produce that query in LINQ??

I am not familiar in LINQ. Apologize me for that.
0
 

Author Comment

by:MartinPetex
ID: 26105411
ralmada,

Your query now works perfectly also. Would you be able to produce it in LINQ at all?

Thanks,

Martin.
0
 

Author Comment

by:MartinPetex
ID: 26105422
sharath,

Thanks again. I will award the points.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 26105429
sorry about that, not familiar with LINQ neither.
0
 

Author Comment

by:MartinPetex
ID: 26105553
sharath,

How can I filter your query on cpo.surname = 'Plongeron'???

Thanks.
0
 

Author Comment

by:MartinPetex
ID: 26105566
sharath,

Figured it out, thanks again.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 26105578
In my case just add a where clause there:
select distinct      
         cpo.firstname,      
        cpo.surname,      
        details = stuff((select distinct ',' + cast(occ.detail as varchar)    
                        from tblCrmContactObjects occ      
                       inner join tblCrmContactObjectLinks ccol on ccol.crm_contact_object_id = occ.crm_contact_object_id 
where cpo.crm_person_object_id = ccol.crm_object_id 
                        order by 1  
                        for xml path('')), 1, 1, '')     
from tblCrmPersonObjects cpo
where cpo.surname = 'Plongeron'

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 26105594
like this
;with CTE as (
select distinct cpo.firstname, cpo.surname, convert(nvarchar,occ.detail) as detail
from tblCrmPersonObjects cpo
inner join tblCrmContactObjectLinks ccol
on cpo.crm_person_object_id = ccol.crm_object_id
inner join tblCrmContactObjects occ
on ccol.crm_contact_object_id = occ.crm_contact_object_id
where cpo.surname = 'Plongeron')

select distinct firstname,surname,
       rtrim(substring(isnull((select ',' + detail from CTE t1 
                                where t1.firstname = t2.firstname and t1.surname = t2.surname for xml path('')),' '),2,2000))
from CTE t2

Open in new window

0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Loops Section Overview
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question