Merge 2 rows into one depending upon some condition-CodePudding

I am using SQL Server and trying to merge rows into one if OrgansationId of the rows are same AND start date of the rows are same AND end date of the rows are same

However if they are different all the rows should be returned as it is.

Is it possible to do it with a single SQL query. Please help

CodePudding user response：

INSERT INTO #TestTable (OrganizationId,StartDate, EndDate)
SELECT * FROM
(
VALUES
    (13506,'2022/08/12','2022/08/12'),
    (13506,'2022/08/12','2022/08/12'),    
    (13506,'2022/08/19','2022/08/19'),
    (13506,'2022/08/19','2022/08/20')
) As RawData (OrganizationId,StartDate, EndDate)


;WITH CTE
AS
(
    SELECT 
        OrganizationId,
        StartDate,
        EndDate,
        ROW_NUMBER() OVER (PARTITION BY OrganizationId,StartDate,EndDate ORDER BY OrganizationId ASC) AS Ordinal
    FROM #TestTable
)

    SELECT 
        OrganizationId,
        StartDate,
        EndDate
    FROM CTE
    WHERE Ordinal = 1

CodePudding user response：

You can try a simple group by

with t (Orgid ,StartDate,EndDate)
as
(
    Select           '13,250'   ,   '2022-12-08 00:00:00',    '2023-01-08 23:59:59'
    Union ALL Select '13,756'   ,   '2022-12-10 00:00:00',    '2023-01-10 23:59:59'
    Union ALL Select '13,250'   ,   '2022-12-08 00:00:00',    '2023-01-08 23:59:59'
)    
Select 
    *
from
    t
group by 
    Orgid,
    StartDate,
    EndDate

CodePudding user response：

It is quite difficult to answer your question without knowing how you get to the result you posted. Is this data from 1 table? Is this the result from a JOIN on multiple tables?

Based on how you get to this result the answer will be different.

From first glance I would say a SELECT DISTINCT should probably do the trick.

If not, this post should probably help you on your way: How to select unique records by SQL