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.
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]