Search code examples
sql-serverdatabasedynamics-crm

Microsoft SQL Query‏ return repeated rows in one row

I need your help for this query:

SELECT        
    'ABBOTT' AS Customername, 'DaleelMAMA' AS AccountName, 
    dbo.AccountBase.Name AS FullName, 
    dbo.AccountBase.Telephone1 AS  MobilePhone, 
    dbo.AccountBase.new_HomePhone1 AS HomePhone, 
    dbo.new_otherparentinformationBase.new_MobilePhone AS ParentPhone, 
    dbo.AccountBase.new_preferredlanguagetext AS Language
FROM            
    dbo.AccountBase 
LEFT OUTER JOIN
    dbo.new_otherparentinformationBase ON dbo.AccountBase.AccountId = 
        dbo.new_otherparentinformationBase.new_MotherName_lookup

This query returns this result set:

The original result 

ABBOTT  DaleelMAMA  Eman    55555555    NULL    56545412    Arabic
ABBOTT  DaleelMAMA  Eman    55555555    NULL    22222222    Arabic
ABBOTT  DaleelMAMA  Eman    55555555    NULL    25456552    Arabic
ABBOTT  DaleelMAMA  Hala    55552504    22252128    NULL    Arabic

but I need to update my query to return this result but as below, if u see u will find that Eman row repeated three time and this because its include different parent phone numbers, so i need to make this result in one row include the mother number and its related parent phones numbers.

required result

required result

please see above link for required result that aim looking about it, thanks for your support in advance.

whole solution look

whole solution look

Solution

  • If it always 4 ParentPhone per Name then try something like this

    ;with cte as
(
SELECT        
    'ABBOTT' AS Customername, 
    'DaleelMAMA' AS AccountName, 
    ROW_NUMBER() over(partition by dbo.AccountBase.Name order by dbo.AccountBase.Telephone1) as rn,
    dbo.AccountBase.Name AS FullName, 
    dbo.AccountBase.Telephone1 AS  MobilePhone, 
    dbo.AccountBase.new_HomePhone1 AS HomePhone, 
    dbo.new_otherparentinformationBase.new_MobilePhone AS ParentPhone, 
    dbo.AccountBase.new_preferredlanguagetext AS Language
FROM            
    dbo.AccountBase 
LEFT OUTER JOIN
    dbo.new_otherparentinformationBase ON dbo.AccountBase.AccountId = dbo.new_otherparentinformationBase.new_MotherName_lookup
    )
SELECT Customername,
       AccountName,
       FullName,
       MobilePhone,
       Max(CASE WHEN rn = 1 THEN ParentPhone END) AS ParentPhone1,
       Max(CASE WHEN rn = 2 THEN ParentPhone END) AS ParentPhone2,
       Max(CASE WHEN rn = 3 THEN ParentPhone END) AS ParentPhone3,
       Max(CASE WHEN rn = 4 THEN ParentPhone END) AS ParentPhone4,
       Language
FROM   cte
GROUP  BY Customername,
          AccountName,
          FullName,
          MobilePhone,
          Language

    Note : If number of ParentPhone per Name is unknown then you need to use Dynamic pivot. Also start using alias name to make the query more readable