Solved

Enhancing my Select Distinct

Posted on 2013-11-15
12
154 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
  • 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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 40

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 40

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 40

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 40

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

821 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