• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 179
  • Last Modified:

Enhancing my Select Distinct

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
swaggrK
Asked:
swaggrK
  • 6
  • 4
  • 2
1 Solution
 
Kyle AbrahamsSenior .Net DeveloperCommented:
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
 
swaggrKAuthor Commented:
@ged325

I am not sure how to incorporate your suggestion into my query
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
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
Independent Software Vendors: 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!

 
swaggrKAuthor Commented:
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
 
swaggrKAuthor Commented:
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
 
SharathData EngineerCommented:
You have different AEA_ContractTypeId for SPT and CAT. Do you want to concatinate these values also? How do you want your result?
0
 
swaggrKAuthor Commented:
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
 
SharathData EngineerCommented:
Do you want AEA_ContractTypeId in your final result set? If yes, how do you want it?
0
 
swaggrKAuthor Commented:
@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
 
SharathData EngineerCommented:
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
 
swaggrKAuthor Commented:
@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
 
SharathData EngineerCommented:
You need to run the whole script. You missed the "from CTE t1" and the CTE part in your query.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 6
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now