?
Solved

Enhancing my Select Distinct

Posted on 2013-11-15
12
Medium Priority
?
175 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
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 

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 750 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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

765 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