Solved

Enhancing my Select Distinct

Posted on 2013-11-15
12
141 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 39

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 39

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
 

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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

706 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now