How to join Two or more row with dynamic result in where clause?

Question

I am trying to join two or three rows using COALESCE on a condition where EntityId is equal in the table so that my output will be like following:

EmailAddress                         EntityId

12_abc@abc.com::::13_pqr@pqr.com     101
12_koi@koi.com::::13_poi@poi.com     102

Right now i am getting following result based on my query:

EmailAddress   EmailTypeId     EntityId

abc@abc.com    12              101
pqr@pqr.com    13              101
koi@koi.com    12              102
poi@poi.com    13              102

The query i am using is as follows:

DECLARE @MyTableVariable TABLE
(

  EmailAddress nvarchar(250),
  EntityIDE int
);

INSERT @MyTableVariable(
  EmailAddress,
  EntityIDE
 )

SELECT  concat([EmailTypeID], '_____', [EmailAddress]))), [EntityID]
FROM [dbo].[Email_Addresses] where [EntityID] in 

(select [EntityID] from [Entities] where [SourcePrimaryKey] in 
    (select [MerchantOwnerID] from [dbo].[Merchant_Owners] where [MerchantID] = 1 ) and
    [EntityTypeID] = (select [EntityTypeID] from [Entity_Types] where [EntityType] = 'MerchantOwner'))

select * from @MyTableVariable

I have defined a table valued parameters and in my select query i have concat two columns into one but i do not know how to use coalesce to join two or more rows where EntityId is same.

My query after where return list of a columns of EntityId

(select [EntityID] from [Entities] where [SourcePrimaryKey] in 
    (select [MerchantOwnerID] from [dbo].[Merchant_Owners] where [MerchantID] = 1 ) and
    [EntityTypeID] = (select [EntityTypeID] from [Entity_Types] where [EntityType] = 'MerchantOwner'))

The above query returns following result set

EntityId

101
102
103
104
...

EDIT

; WITH CTE AS (
    SELECT EA.[EmailTypeID], EA.[EmailAddress], EA.[EntityID]
    FROM [dbo].[Email_Addresses] EA
    JOIN [Entities] E ON E.[EntityID] = EA.[EntityID]
    JOIN (SELECT [MerchantOwnerID] FROM [dbo].[Merchant_Owners] WHERE [MerchantID] = 1) M ON M.[MerchantOwnerID] = E.[SourcePrimaryKey]
    JOIN (SELECT [EntityTypeID] FROM [Entity_Types] WHERE [EntityType] = 'MerchantOwner') ET ON ET.[EntityTypeID] = E.[EntityTypeID])
    ,
    ABC
    As
    (
    SELECT EB.[PhoneNumber], EB.[EntityID], EB.[PhoneType]
    FROM [dbo].[Phones] EB
    JOIN [Entities] E ON E.[EntityID] = EB.[EntityID]
    JOIN (SELECT [MerchantOwnerID] FROM [dbo].[Merchant_Owners] WHERE [MerchantID] = 1) M ON M.[MerchantOwnerID] = E.[SourcePrimaryKey]
    JOIN (SELECT [EntityTypeID] FROM [Entity_Types] WHERE [EntityType] = 'MerchantOwner') ET ON ET.[EntityTypeID] = E.[EntityTypeID]
    )
SELECT STUFF((SELECT '::::' + CONVERT(NVARCHAR, [EmailTypeID]) + '_' + [EmailAddress]
            FROM CTE
            WHERE [EntityID] = T1.[EntityID]
            ORDER BY [EmailTypeID]
            FOR XML PATH ('')),1,4,'') [EmailAddress],
            T1.[EntityID],
            STUFF((SELECT '::::' + CONVERT(NVARCHAR, [PhoneType]) + '_' + [PhoneNumber]
            FROM ABC
            WHERE [EntityID] = T2.[EntityID]
            ORDER BY [PhoneType]
            FOR XML PATH ('')),1,4,'') [PhoneNumber],
            T2.[EntityID] 

FROM CTE T1, ABC T2
GROUP BY T1.[EntityID],T2.[EntityID]

This result is giving me 56 rows my one CTE returns 8 rows and another CTE (ABC) as 7 rows

i do not know why it is multiplying

EDIT2

; WITH CTE AS (
    SELECT EA.[EmailTypeID], EA.[EmailAddress], EA.[EntityID],
 row_number() over(partition by EA.EntityID order by EA.EntityID desc) as rn
    FROM [dbo].[Email_Addresses] EA
    JOIN [Entities] E ON E.[EntityID] = EA.[EntityID]
    JOIN (SELECT [MerchantOwnerID] FROM [dbo].[Merchant_Owners] WHERE [MerchantID] = 1) M ON M.[MerchantOwnerID] = E.[SourcePrimaryKey]
    JOIN (SELECT [EntityTypeID] FROM [Entity_Types] WHERE [EntityType] = 'MerchantOwner') ET ON ET.[EntityTypeID] = E.[EntityTypeID]
 ),
 ABC
 As
 (
 SELECT EB.[PhoneNumber], EB.[EntityID], EB.[PhoneType],
 row_number() over(partition by EB.EntityID order by EB.EntityID desc) as rn
    FROM [dbo].[Phones] EB
    JOIN [Entities] E ON E.[EntityID] = EB.[EntityID]
    JOIN (SELECT [MerchantOwnerID] FROM [dbo].[Merchant_Owners] WHERE [MerchantID] = 1) M ON M.[MerchantOwnerID] = E.[SourcePrimaryKey]
    JOIN (SELECT [EntityTypeID] FROM [Entity_Types] WHERE [EntityType] = 'MerchantOwner') ET ON ET.[EntityTypeID] = E.[EntityTypeID]
 ),
 GHK
 As
 (
  SELECT EB.[Address1], EB.[City], EB.[State],EB.[EntityID],
  row_number() over(partition by EB.EntityID order by EB.EntityID desc) as rn
    FROM [dbo].[Addresses] EB
    JOIN [Entities] E ON E.[EntityID] = EB.[EntityID]
    JOIN (SELECT [MerchantOwnerID] FROM [dbo].[Merchant_Owners] WHERE [MerchantID] = 1) M ON M.[MerchantOwnerID] = E.[SourcePrimaryKey]
    JOIN (SELECT [EntityTypeID] FROM [Entity_Types] WHERE [EntityType] = 'MerchantOwner') ET ON ET.[EntityTypeID] = E.[EntityTypeID]
 ),
 PQR
 As
 (
 SELECT EB.[MerchantOwnerID], EB.[FirstName], EB.[LastName],EB.[BusinessTitle],
 EB.[OwnershipPercentage], EB.[DateOfBirth],E.[EntityID],
  row_number() over(partition by E.EntityID order by E.EntityID desc) as rn
    FROM [dbo].[Merchant_Owners] EB
    JOIN [Entities] E ON E.[SourcePrimaryKey] = EB.[MerchantOwnerID] and [EntityTypeID]=2
    JOIN (SELECT [MerchantOwnerID] FROM [dbo].[Merchant_Owners] WHERE [MerchantID] = 1) M ON M.[MerchantOwnerID] = E.[SourcePrimaryKey]
    JOIN (SELECT [EntityTypeID] FROM [Entity_Types] WHERE [EntityType] = 'MerchantOwner') ET ON ET.[EntityTypeID] = E.[EntityTypeID]
 )

SELECT STUFF((SELECT '::::' + CONVERT(NVARCHAR, [EmailTypeID]) + '_' + [EmailAddress]
            FROM CTE
            WHERE [EntityID] = c.[EntityID]
            ORDER BY [EmailTypeID]
            FOR XML PATH ('')),1,4,'') [EmailAddress],
   c.[EntityID],
   STUFF((SELECT '::::' + CONVERT(NVARCHAR, [PhoneType]) + '_' + [PhoneNumber]
            FROM ABC
            WHERE [EntityID] = c2.[EntityID]
            ORDER BY [PhoneType]
            FOR XML PATH ('')),1,4,'') [PhoneNumber],
   c2.[EntityID] as pid,
  c3.[City],c3.[State],c3.[EntityID], c4.[FirstName]

FROM CTE c  FULL JOIN ABC c2 ON c.EntityID = c2.EntityID,
GHK c3, PQR c4
--FULL JOIN
--GHK c3 ON c.EntityID = c3.EntityID

  WHERE c.rn = 1 OR c2.rn = 1 or c3.rn=1or c4.rn=1
  group by  c.[EntityID],c2.[EntityID],c3.EntityID,c4.EntityID

It throws error Column 'GHK.City' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

if i remove this field then it throws error at GHK.State and so on...

my CTE (GHK) already returning 7 rows so i do not think their is any need for aggregate it.

Any idea how to get around.

My CTE(PQR) is returning distict rows also. and i am getting same error with it too.

Look at some [recursive cte concatenation](http://stackoverflow.com/questions/3151399/is-it-possible-to-concatenate-column-values-into-a-string-using-cte) examples. — Glenn, Dec 17 '15 at 04:09
If not coalesce then anything else that can do the job for me @Jasen — lokopi, Dec 17 '15 at 04:13
you want some sort of aggregate function that concatenates strings, — Jasen, Dec 17 '15 at 04:37
I am struck here for two days , i looked at some aggregate functions too but no luck, any thing specific @Jasen — lokopi, Dec 17 '15 at 04:43

ZLK · Accepted Answer · 2015-12-17T21:47:12.950

Depending on what output you're expecting, one of these would work.

If you're expecting individual results for each EmailTypeID (e.g. 12:::13, 12:::14, 13:::14) then the following would work.

DECLARE @MyTableVariable TABLE([EmailAddress] NVARCHAR(100),[EmailTypeID] INT, [EntityID] INT)
INSERT @MyTableVariable VALUES ('abc@abc.com',12,101)
,('pqr@pqr.com',13,101)
,('koi@koi.com',12,102)
,('poi@poi.com',13,102)
,('blah@blah.com',14,102)
SELECT CONVERT(NVARCHAR, T1.[EmailTypeID]) + '_' + T1.[EmailAddress] + '::::' + CONVERT(NVARCHAR, T2.[EmailTypeID]) + '_' + T2.[EmailAddress] [EmailAddress]
    , T1.[EntityID]
FROM @MyTableVariable T1
JOIN @MyTableVariable T2 ON T1.[EntityID] = T2.[EntityID] AND T2.[EmailTypeID] > T1.[EmailTypeID]

If you're expecting just a single concatenation, then this would work:

DECLARE @MyTableVariable2 TABLE([EmailAddress] NVARCHAR(100),[EmailTypeID] INT, [EntityID] INT)
INSERT @MyTableVariable2 VALUES ('abc@abc.com',12,101)
,('pqr@pqr.com',13,101)
,('koi@koi.com',12,102)
,('poi@poi.com',13,102)
,('blah@blah.com',14,102)
SELECT STUFF((SELECT '::::' + CONVERT(NVARCHAR, [EmailTypeID]) + '_' + [EmailAddress]
            FROM @MyTableVariable2
            WHERE [EntityID] = T1.[EntityID]
            ORDER BY [EmailTypeID]
            FOR XML PATH ('')),1,4,'') [EmailAddress]
    , [EntityID]
FROM @MyTableVariable2 T1
GROUP BY [EntityID]

As a side note, I think your query would look more readable as a series of joins rather than a series of IN statements. e.g.

SELECT EA.[EmailTypeID], EA.[EmailAddress], EA.[EntityID]
FROM [dbo].[Email_Addresses] EA
JOIN [Entities] E ON E.[EntityID] = EA.[EntityID]
JOIN (SELECT [MechantOwnerID] FROM [dbo].[MechantOwners] WHERE [MechantID] = 1) M ON M.[MerchantOwnerID] = E.[SourcePrimaryKey]
JOIN (SELECT [EntityTypeID] FROM [Entity_Types] WHERE [EntityType] = 'MerchantOwner') ET ON ET.[EntityTypeID] = E.[EntityTypeID]

EDIT (all together to show the entire query):

; WITH CTE AS (
    SELECT EA.[EmailTypeID], EA.[EmailAddress], EA.[EntityID]
    FROM [dbo].[Email_Addresses] EA
    JOIN [Entities] E ON E.[EntityID] = EA.[EntityID]
    JOIN (SELECT [MechantOwnerID] FROM [dbo].[MechantOwners] WHERE [MechantID] = 1) M ON M.[MerchantOwnerID] = E.[SourcePrimaryKey]
    JOIN (SELECT [EntityTypeID] FROM [Entity_Types] WHERE [EntityType] = 'MerchantOwner') ET ON ET.[EntityTypeID] = E.[EntityTypeID])
SELECT STUFF((SELECT '::::' + CONVERT(NVARCHAR, [EmailTypeID]) + '_' + [EmailAddress]
            FROM CTE
            WHERE [EntityID] = T1.[EntityID]
            ORDER BY [EmailTypeID]
            FOR XML PATH ('')),1,4,'') [EmailAddress]
    , [EntityID]
FROM CTE T1
GROUP BY [EntityID]

If you prefer using your current query (or if my one doesn't work for some reason), just edit what's in the CTE.

EDIT2 (with the phone number stuff added):

; WITH CTE AS (
    SELECT EA.[EmailTypeID], EA.[EmailAddress], EA.[EntityID]
    FROM [dbo].[Email_Addresses] EA
    JOIN [Entities] E ON E.[EntityID] = EA.[EntityID]
    JOIN (SELECT [MerchantOwnerID] FROM [dbo].[Merchant_Owners] WHERE [MerchantID] = 1) M ON M.[MerchantOwnerID] = E.[SourcePrimaryKey]
    JOIN (SELECT [EntityTypeID] FROM [Entity_Types] WHERE [EntityType] = 'MerchantOwner') ET ON ET.[EntityTypeID] = E.[EntityTypeID]
),
ABC
As
(
    SELECT EB.[PhoneNumber], EB.[EntityID], EB.[PhoneType]
    FROM [dbo].[Phones] EB
    JOIN [Entities] E ON E.[EntityID] = EB.[EntityID]
    JOIN (SELECT [MerchantOwnerID] FROM [dbo].[Merchant_Owners] WHERE [MerchantID] = 1) M ON M.[MerchantOwnerID] = E.[SourcePrimaryKey]
    JOIN (SELECT [EntityTypeID] FROM [Entity_Types] WHERE [EntityType] = 'MerchantOwner') ET ON ET.[EntityTypeID] = E.[EntityTypeID]
)


SELECT STUFF((SELECT '::::' + CONVERT(NVARCHAR, [EmailTypeID]) + '_' + [EmailAddress]
        FROM CTE
        WHERE [EntityID] = T1.[EntityID]
        ORDER BY [EmailTypeID]
        FOR XML PATH ('')),1,4,'') [EmailAddress],
        T1.[EntityID],
        STUFF((SELECT '::::' + CONVERT(NVARCHAR, [PhoneType]) + '_' + [PhoneNumber]
        FROM ABC
        WHERE [EntityID] = T1.[EntityID]
        ORDER BY [PhoneType]
        FOR XML PATH ('')),1,4,'') [PhoneNumber]
FROM CTE T1
LEFT JOIN ABC T2 ON T2.[EntityID] = T1.[EntityID]
GROUP BY T1.[EntityID]

EDIT3:

SELECT COALESCE(c.[EntityID], c2.[EntityID], c3.[EntityID], c4.[EntityID]) [EntityID]
    , STUFF((SELECT '::::' + CONVERT(NVARCHAR, [EmailTypeID]) + '_' + [EmailAddress]
        FROM CTE
        WHERE [EntityID] = c.[EntityID]
        ORDER BY [EmailTypeID]
        FOR XML PATH ('')),1,4,'') [EmailAddress]
    , STUFF((SELECT '::::' + CONVERT(NVARCHAR, [PhoneType]) + '_' + [PhoneNumber]
        FROM ABC
        WHERE [EntityID] = c2.[EntityID]
        ORDER BY [PhoneType]
        FOR XML PATH ('')),1,4,'') [PhoneNumber]
    , c3.[City], c3.[State], c4.[FirstName]
FROM CTE c  
FULL JOIN ABC c2 ON c.EntityID = c2.EntityID
FULL JOIN (SELECT * FROM GHK WHERE RN = 1) c3 ON c3.[EntityID] = c.[EntityID] OR c3.[EntityID] = c2.[EntityID]
FULL JOIN (SELECT * FROM PQR WHERE RN = 1) c4 ON c4.[EntityID] = c.[EntityID] OR c4.[EntityID] = c2.[EntityID] OR c4.[EntityID] = c3.[EntityID]
GROUP BY c.[EntityID], c2.[EntityID], c3.[EntityID], c4.[EntityID], c3.[City], c3.[State], c4.[FirstName]

As an example of how using UNION could work with your current query, this should work (and avoid the need to GROUP BY because you are only producing one row per entityID). EDIT 4:

; WITH CTE AS (
    SELECT EA.[EmailTypeID], EA.[EmailAddress], EA.[EntityID]
    FROM [dbo].[Email_Addresses] EA
    JOIN [Entities] E ON E.[EntityID] = EA.[EntityID]
    JOIN (SELECT [MerchantOwnerID] FROM [dbo].[Merchant_Owners] WHERE [MerchantID] = 1) M ON M.[MerchantOwnerID] = E.[SourcePrimaryKey]
    JOIN (SELECT [EntityTypeID] FROM [Entity_Types] WHERE [EntityType] = 'MerchantOwner') ET ON ET.[EntityTypeID] = E.[EntityTypeID]
    )
, ABC AS (
    SELECT EB.[PhoneNumber], EB.[EntityID], EB.[PhoneType]
    FROM [dbo].[Phones] EB
    JOIN [Entities] E ON E.[EntityID] = EB.[EntityID]
    JOIN (SELECT [MerchantOwnerID] FROM [dbo].[Merchant_Owners] WHERE [MerchantID] = 1) M ON M.[MerchantOwnerID] = E.[SourcePrimaryKey]
    JOIN (SELECT [EntityTypeID] FROM [Entity_Types] WHERE [EntityType] = 'MerchantOwner') ET ON ET.[EntityTypeID] = E.[EntityTypeID]
    )
, GHK AS (
    SELECT EB.[Address1], EB.[City], EB.[State],EB.[EntityID],
        ROW_NUMBER() OVER(PARTITION BY E.EntityID ORDER BY E.EntityID) AS RN
    FROM [dbo].[Addresses] EB
    JOIN [Entities] E ON E.[EntityID] = EB.[EntityID]
    JOIN (SELECT [MerchantOwnerID] FROM [dbo].[Merchant_Owners] WHERE [MerchantID] = 1) M ON M.[MerchantOwnerID] = E.[SourcePrimaryKey]
    JOIN (SELECT [EntityTypeID] FROM [Entity_Types] WHERE [EntityType] = 'MerchantOwner') ET ON ET.[EntityTypeID] = E.[EntityTypeID]
    )
, PQR AS (
    SELECT EB.[FirstName], E.[EntityID],
        -- EB.[MerchantOwnerID], EB.[LastName],EB.[BusinessTitle], EB.[OwnershipPercentage], EB.[DateOfBirth],
        ROW_NUMBER() OVER(PARTITION BY E.EntityID ORDER BY E.EntityID) AS RN
    FROM [dbo].[Merchant_Owners] EB
    JOIN [Entities] E ON E.[SourcePrimaryKey] = EB.[MerchantOwnerID] and [EntityTypeID]=2
    JOIN (SELECT [MerchantOwnerID] FROM [dbo].[Merchant_Owners] WHERE [MerchantID] = 1) M ON M.[MerchantOwnerID] = E.[SourcePrimaryKey]
    JOIN (SELECT [EntityTypeID] FROM [Entity_Types] WHERE [EntityType] = 'MerchantOwner') ET ON ET.[EntityTypeID] = E.[EntityTypeID]
    )
, EntityList AS (
    SELECT [EntityID] FROM CTE
    UNION
    SELECT [EntityID] FROM ABC
    UNION
    SELECT [EntityID] FROM GHK
    UNION
    SELECT [EntityID] FROM PQR
    )
SELECT EL.[EntityID]
    , STUFF((SELECT '::::' + CONVERT(NVARCHAR, [EmailTypeID]) + '_' + [EmailAddress]
            FROM CTE
            WHERE [EntityID] = EL.[EntityID]
            ORDER BY [EmailTypeID]
            FOR XML PATH ('')),1,4,'') [EmailAddress]
    , STUFF((SELECT '::::' + CONVERT(NVARCHAR, [PhoneType]) + '_' + [PhoneNumber]
            FROM ABC
            WHERE [EntityID] = EL.[EntityID]
            ORDER BY [PhoneType]
            FOR XML PATH ('')),1,4,'') [PhoneNumber]
    , c3.[City], c3.[State], c4.[FirstName]
FROM EntityList EL
LEFT JOIN CTE c1 ON c1.[EntityID] = EL.[EntityID]
LEFT JOIN ABC c2 ON c2.[EntityID] = EL.[EntityID]
LEFT JOIN (SELECT * FROM GHK WHERE RN = 1) c3 ON c3.[EntityID] = EL.[EntityID]
LEFT JOIN (SELECT * FROM PQR WHERE RN = 1) c4 ON c4.[EntityID] = EL.[EntityID]
ORDER BY EL.[EntityID]

Thank You but can you please explain following code to me i am never too comfy with XML thing in sql :) `SELECT STUFF((SELECT '::::' + CONVERT(NVARCHAR, [EmailTypeID]) + '_' + [EmailAddress] FROM @MyTableVariable2 WHERE [EntityID] = T1.[EntityID] ORDER BY [EmailTypeID] FOR XML PATH ('')),1,4,'') [EmailAddress] , [EntityID]` — lokopi, Dec 17 '15 at 05:04
I will use your second code below this _If you're expecting just a single concatenation, then this would work:_ — lokopi, Dec 17 '15 at 05:06
FOR XML just generates XML for the query results, except if the PATH is '' THEN there's no <> to begin the XML. Likewise, if the column you are selecting has no alias then there's no <> for each of the selections. So all you get is the raw results concatenated. The STUFF statement is to get rid of the leading '::::' that would result from it. — ZLK, Dec 17 '15 at 05:11
Try changing the STUFF statement to this to test it: `STUFF((SELECT '::::' + CONVERT(NVARCHAR, [EmailTypeID]) + '_' + [EmailAddress] [A] FROM @MyTableVariable2 WHERE [EntityID] = T1.[EntityID] ORDER BY [EmailTypeID] FOR XML PATH ('B')),1,0,'')` Then try removing the '[A]' and 'B' parts to see what they do. Best way to learn is trial and error. — ZLK, Dec 17 '15 at 05:13
Hello, what i am getting is it is a self reference to the `@MyTableVariable2` how i am supposed to get the value from a table and then insert it into the `@MyTableVariable2` — lokopi, Dec 17 '15 at 05:13
Well, you don't need the table variable in the first place, really. I'll edit my answer to show how I'd do it. — ZLK, Dec 17 '15 at 05:14
The CTE is my prefered method of getting your desired results, if you prefer using your original query, just edit what's in the CTE. If you prefer using an inline view instead of a CTE, just change it to an inline view. — ZLK, Dec 17 '15 at 05:18
hey i just Edit my question can you please take a look at it i am getting error `Ambiguous column name 'EntityID'.` — lokopi, Dec 17 '15 at 05:55
You're getting an ambiguous column name because the "[EntityID]" after the stuff statement doesn't have an alias. Try "T1.[EntityID]". Also you may need to group by "T1.[EntityID]", too. And also the WHERE statement on the second stuff statement could just be on T1.[EntityID], since you're joining the CTEs anyway. — ZLK, Dec 17 '15 at 06:15
See EDIT2 for how you should be able to achieve what you're trying to achieve in a simpler way. — ZLK, Dec 17 '15 at 06:31
The PhoneNumber Column is in Another table not in the same table pelase check my EDIT again, I have updated it — lokopi, Dec 17 '15 at 07:07
It's because you're grouping by both T1.EntityID and T2.EntityID. When you group by them both it is basically telling the query to find every combination of EntityID and group it with every combination of entityIDs from the other table. See my edit2 now. — ZLK, Dec 17 '15 at 07:17
it is giving me this error `ORDER BY items must appear in the select list if SELECT DISTINCT is specified.` — lokopi, Dec 17 '15 at 07:20
Edited again, sorry. The ORDER BY in the second STUFF statement should be a convert statement. Should work now. — ZLK, Dec 17 '15 at 07:22
Also you don't need to do distinct either, I just added that for a different reason. You can take the DISTINCT out. — ZLK, Dec 17 '15 at 07:23
WIthout Distinct it is giving me error too `Column 'ABC.EntityID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.` — lokopi, Dec 17 '15 at 07:24
Oh. Yeah I forgot to change the T2 in the second STUFF statement to T1. Should be fine now. NOTE: IF there are EntityIDs that exist in ABC but not in CTE then you will need to do a full join and not a left join. — ZLK, Dec 17 '15 at 07:36
Hey i just ran in to a problem can you please check it i added two new CTE which return distinct rows so no need to aggregate any columns but still i am getting error. — lokopi, Dec 17 '15 at 09:03
Well you're getting that error because city, state and firstname aren't in your group by clause (they should be). I made an EDIT3 which I *think* is what you're looking for here, but it's hard to be certain without knowing the actual data. — ZLK, Dec 17 '15 at 13:09
Also, if it gets any more complex, the easiest thing to do would be to produce a list of all entityIDs in all of the CTEs (e.g. `, EntityList AS (SELECT [EntityID] FROM CTE1 UNION SELECT [EntityID] FROM CTE2...` and then running a select statement that selects all of those and left joins all the other CTEs based on conditions. I might edit in what I mean tomorrow if this is unclear. — ZLK, Dec 17 '15 at 13:11
See EDIT4 as an example of UNION for this (to avoid the need to GROUP BY) — ZLK, Dec 17 '15 at 21:47

How to join Two or more row with dynamic result in where clause?

1 Answers1