Solved

Enhancing my Select Distinct

Posted on 2013-11-15
12
163 Views
Last Modified: 2014-08-26
In my query below, my results output a separate row for each contract type.
I would like to alter this query so that it returns one DISTINCT row for each contract type.
I would like to do this either using a subquery or a function.

In the attached spreadsheet you will notice that American Theatre Company, has two different contract types(SPT and CAT). I would prefer the results to look like (SPT, CAT) for a single account.


 select Distinct AccountId,
                AEA_employeragreementId,
                Name, 
                Address1_AddressId, 
                Address1_AddressTypeCode,
                Address1_City,
                Address1_Line1,
                Address1_Line2,
                Address1_Line3,
                Address1_Fax,
                Address1_PostalCode,
--                Address1_StateOrProvince,
                Address1_Telephone1,
                Address1_Name,
                AEA_Address1_StateName AS AEA_Address1StateName,
                aea_address1_countryname AS Address1_Country,
                WebSiteURL,
                FilteredAEA_contracttype.AEA_ContractTypeId,
                AEA_contracttypename,
--                AttributeName,
--                Value as 'StateName',
                FilteredAEA_ContractType.aea_publishtowebname  
from FilteredAccount AS CRMAF_FilteredAccount
    inner join FilteredAEA_employeragreement
on AccountId = AEA_EmployerOrganizationId     
    inner join FilteredAEA_contracttype
on FilteredAEA_employeragreement.AEA_contracttypeId = FilteredAEA_contracttype.AEA_ContractTypeId 
inner join Filteredaea_postalcode
on Filteredaea_postalcode.aea_zip = Left(CRMAF_FilteredAccount.address1_postalcode,5)
inner join Filteredaea_aea_postalcode_aea_locale
on Filteredaea_aea_postalcode_aea_locale.aea_postalcodeid = Filteredaea_postalcode.aea_postalcodeid
inner join Filteredaea_locale
on Filteredaea_aea_postalcode_aea_locale.aea_localeid = Filteredaea_locale.aea_localeid
--    left outer join FilteredStringMap
--	on AttributeValue = AEA_Address1State and AttributeName = 'aea_state'
  where aea_areaname like 'Chicago'   and CRMAF_FilteredAccount.statecodename = 'Active' and  
  (AEA_AgreementEndDate Is NULL or
  (DateDiff(dd, AEA_AgreementEndDate,GETDATE()) > 0 and DateDiff(dd, AEA_AgreementEndDate,GETDATE()) < 365))
  order by Name   

Open in new window

directory-a.xlsx
0
Comment
Question by:swaggrK
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
12 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39651776
select
--columns
isnull((
select
      stuff ((
      select distinct ', ' + AEA_contracttypename from <table> t
      where
                --enter in condtions so t.<col> = ParentTable.<col>
      for XML PATH('')
      ), 1,2, '')), '') ContractTypes
      

from #temp a
0
 

Author Comment

by:swaggrK
ID: 39651811
@ged325

I am not sure how to incorporate your suggestion into my query
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39652043
take out the AEA_contracttypename  and anything else relating to the contract that would cause duplicate records.


Add
isnull((
select
      stuff ((
      select distinct ', ' + AEA_contracttypename from <table> t
      where
                --enter in condtions so t.<col> = ParentTable.<col>
      for XML PATH('')
      ), 1,2, '')), '') ContractTypes

essentially what this does it concatenate rows this is an inner query that pulls the contract types based on the conditions.

let's say you had Customers and orders

it would be

select
c.Id,
c.Name,
--etc
 stuff ((
      select distinct ', ' + o.OrderNumber from Orders o
      where
       o.CustomerId = c.Id
      for XML PATH('')
      ), 1,2, '')), '') OrderNumbers
from
Customers c
0
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 

Author Comment

by:swaggrK
ID: 39652332
I am getting the same results.

 select Distinct AccountId,
                AEA_employeragreementId,
                Name, 
                Address1_AddressId, 
                Address1_AddressTypeCode,
                Address1_City,
                Address1_Line1,
                Address1_Line2,
                Address1_Line3,
                Address1_Fax,
                Address1_PostalCode,
--                Address1_StateOrProvince,
                Address1_Telephone1,
                Address1_Name,
                AEA_Address1_StateName AS AEA_Address1StateName,
                aea_address1_countryname AS Address1_Country,
                WebSiteURL,
                FilteredAEA_contracttype.AEA_ContractTypeId, 
                isnull((
select
      stuff ((
      select distinct ', ' + AEA_contracttypename from FilteredAEA_contracttype t
      where t.AEA_contracttypename = FilteredAEA_contracttype.AEA_contracttypename
      for XML PATH('')
      ), 1,2, '')), '') ContractTypes,
--                AttributeName,
--                Value as 'StateName',
                FilteredAEA_ContractType.aea_publishtowebname  
from FilteredAccount AS CRMAF_FilteredAccount
    inner join FilteredAEA_employeragreement
		on AccountId = AEA_EmployerOrganizationId     
    inner join FilteredAEA_contracttype
		on FilteredAEA_employeragreement.AEA_contracttypeId = FilteredAEA_contracttype.AEA_ContractTypeId 
	inner join Filteredaea_postalcode
		on Filteredaea_postalcode.aea_zip = Left(CRMAF_FilteredAccount.address1_postalcode,5)
	inner join Filteredaea_aea_postalcode_aea_locale
		on Filteredaea_aea_postalcode_aea_locale.aea_postalcodeid = Filteredaea_postalcode.aea_postalcodeid
	inner join Filteredaea_locale
		on Filteredaea_aea_postalcode_aea_locale.aea_localeid = Filteredaea_locale.aea_localeid
--    left outer join FilteredStringMap
--		on AttributeValue = AEA_Address1State and AttributeName = 'aea_state'
  where aea_areaname like 'Chicago'   and CRMAF_FilteredAccount.statecodename = 'Active' and  
  (AEA_AgreementEndDate Is NULL or
  (DateDiff(dd, AEA_AgreementEndDate,GETDATE()) > 0 and DateDiff(dd, AEA_AgreementEndDate,GETDATE()) < 365))
  order by Name 

Open in new window

0
 

Author Comment

by:swaggrK
ID: 39652426
Typo:
AEA_contracttype is the table

select
      stuff ((
      select distinct ', ' + AEA_contracttypename from AEA_contracttype t
      where t.AEA_contracttypename = FilteredAEA_contracttype.AEA_contracttypename
      for XML PATH('')
      ), 1,2, '')), '') ContractTypes,

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 39653439
You have different AEA_ContractTypeId for SPT and CAT. Do you want to concatinate these values also? How do you want your result?
0
 

Author Comment

by:swaggrK
ID: 39656649
I would like my results to appear like this:

Name                                         AEA_contracttypename
American Theatre Company      CAT, SPT 

Open in new window


Instead of this:
Name                                         AEA_contracttypename
American Theatre Company      CAT 
American Theatre Company      SPT 

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 39670753
Do you want AEA_ContractTypeId in your final result set? If yes, how do you want it?
0
 

Author Comment

by:swaggrK
ID: 39671634
@Sharath_123, no it does not need to be in the result set.
I merely need the results to look like this, with Name distinct and  
AEA_contracttypename separated by commas:

Name                                         AEA_contracttypename
American Theatre Company      CAT, SPT 

Open in new window


Currently, the output looks like this, with Name occupying  a new row everytime there
 is a new AEA_contracttypename. I do not want it to be like this any longer:

Name                                         AEA_contracttypename
American Theatre Company      CAT 
American Theatre Company      SPT 

Open in new window

0
 
LVL 41

Accepted Solution

by:
Sharath earned 250 total points
ID: 39672225
give a try.
 ;with CTE as (
 select Distinct AccountId,
                AEA_employeragreementId,
                Name, 
                AEA_contracttypename
from FilteredAccount AS CRMAF_FilteredAccount
    inner join FilteredAEA_employeragreement
on AccountId = AEA_EmployerOrganizationId     
    inner join FilteredAEA_contracttype
on FilteredAEA_employeragreement.AEA_contracttypeId = FilteredAEA_contracttype.AEA_ContractTypeId 
inner join Filteredaea_postalcode
on Filteredaea_postalcode.aea_zip = Left(CRMAF_FilteredAccount.address1_postalcode,5)
inner join Filteredaea_aea_postalcode_aea_locale
on Filteredaea_aea_postalcode_aea_locale.aea_postalcodeid = Filteredaea_postalcode.aea_postalcodeid
inner join Filteredaea_locale
on Filteredaea_aea_postalcode_aea_locale.aea_localeid = Filteredaea_locale.aea_localeid
--    left outer join FilteredStringMap
--	on AttributeValue = AEA_Address1State and AttributeName = 'aea_state'
  where aea_areaname like 'Chicago'   and CRMAF_FilteredAccount.statecodename = 'Active' and  
  (AEA_AgreementEndDate Is NULL or
  (DateDiff(dd, AEA_AgreementEndDate,GETDATE()) > 0 and DateDiff(dd, AEA_AgreementEndDate,GETDATE()) < 365)))
SELECT DISTINCT AccountId,AEA_employeragreementId,Name
       STUFF(( SELECT DISTINCT ','+CONVERT(VARCHAR,AEA_contracttypename)
                 FROM  CTE AS t2 
			   WHERE t1.AccountId = t2.AccountId 
				 AND t1.AEA_employeragreementId = t2.AEA_employeragreementId
				 AND t1.Name = t2.Name
                 FOR XML PATH('')), 1, 1, '') AS AEA_contracttypename 
  from CTE t1
  order by Name   

Open in new window

0
 

Author Comment

by:swaggrK
ID: 39681500
@Sharath_123,

I am getting the following errors in the following block of code:

SELECT DISTINCT AccountId, AEA_employeragreementId, Name
       STUFF(( SELECT DISTINCT ','+CONVERT(VARCHAR,AEA_contracttypename)
                 FROM  CTE AS t2 
			   WHERE t1.AccountId = t2.AccountId 
				 AND t1.AEA_employeragreementId = t2.AEA_employeragreementId
				 AND t1.Name = t2.Name
                 FOR XML PATH('')), 1, 1, '') AS AEA_contracttypename 

Open in new window



1.  Invlaid column names for AccountId, AEA_employeragreementId, Name    

2. In the Where clause I am getting "the multi-part identifier could not be bound" for:  t1.AccountId = t2.AccountId
t1.AEA_employeragreementId = t2.AEA_employeragreementId
t1.Name = t2.Name
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39682440
You need to run the whole script. You missed the "from CTE t1" and the CTE part in your query.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

690 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