Merge 2 rows into one depending upon some condition

Question

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

You should take a look at inner joins: https://www.w3schools.com/sql/sql_join_inner.asp — Natrium, Dec 21 '22 at 07:50
Please show sample data (more than only one or two rows) and the expected result. It's unclear if this data comes from one or more tables, it's unclear if you need a join, group by clause, distinct or whatever. — Jonas Metzler, Dec 21 '22 at 07:53
As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. — Dale K, Dec 21 '22 at 08:23

score 0 · Answer 1 · answered Dec 21 '22 at 08:01

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

*If* this Is the answer, we have a conanical [duplicate](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group). — Thom A, Dec 21 '22 at 08:43

score -1 · Answer 2 · edited Dec 21 '22 at 08:24

-1

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

edited Dec 21 '22 at 08:24

Dale K

25,246
15
42
71

answered Dec 21 '22 at 07:55

Stigi

104
3

3

This is a bad idea even though it works *in this case*. You can't have non-group columns in SELECT, so that `SELECT *` can easily cause problems. Adding one more column would raise an error. The simple answer would be `SELECT DISTINCT`, but a better answer would be to avoid the duplicate rows in the first place – Panagiotis Kanavos Dec 21 '22 at 08:00
The problem with SELECT DISTINCT is that if the OP would like to return the rest of the data as well and this could differ, he would not be able to filter out the dups (according to the three columns mentioned above). If the result is restricted to these three columns I agree that DISTINCTing them is a great and easy solution, if more has to be returned, he might want to look at the ROWNUMBER example I provided where you can also specify by the order which one would be the most relevant (Maybe there is a timestamp etc. in the able definition itself, which we don´t know from the short expl) – Jens K. Süßmeyer Dec 21 '22 at 08:04
yes you are right , there are other columns which are not same , the requirement is to return any one the row if organisationId , start date and end date match. – Spandana Dec 21 '22 at 08:09
I would the careful by *any* row. There is probably an indicator / condition you might want to use, whether the most current (if there is a timestamp in the row) o some other predicates you might be able to use. Queries should be written as deterministic as possible making sure that testing them will bring the same result as later applying them to the database. – Jens K. Süßmeyer Dec 21 '22 at 08:16
This is my query with all the joins ------SELECT ol.OrganizationId, ol.StartDate , ol.EndDate , ol.ProductId, pt.Name from SU_AdminTool.dbo.OrganizationLicense ol, SU_AdminTool.dbo.Product p , SU_AdminTool.dbo.ProductType pt where ol.EndDate >= GETDATE() and ol.ProductId = p.Id and p.ProductTypeId = pt.Id GROUP BY ol.OrganizationId, ol.ProductId, pt.Name, ol.StartDate , ol.EndDate ORDER BY ol.OrganizationId desc – Spandana Dec 21 '22 at 08:38
We are going to merge 2 products into 1 while migrating to a new database so based on that I am trying to run a single query which will check if rows has same organisation Id , start date and end date than return just 1 row back – Spandana Dec 21 '22 at 08:42

score -2 · Answer 3 · answered Dec 21 '22 at 07:57

-2

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

answered Dec 21 '22 at 07:57

TTrain

1

1

An answer needs to fully answer the question, not just offer some tips - they belong in comments. – Dale K Dec 21 '22 at 08:24

Merge 2 rows into one depending upon some condition

3 Answers3