Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Trying to merge rows into one row with certain conditions

Given 2 or more rows that are selected to merge, one of them is identified as being the template row. The other rows should merge their data into any null value columns that the template has.

Example data:

Id  Name     Address          City          State   Active  Email             Date
1   Acme1    NULL             NULL          NULL    NULL    [email protected]     3/1/2011
2   Acme1    1234 Abc Rd      Springfield   OR      0       [email protected]    1/12/2012
3   Acme2    NULL             NULL          NULL    1       [email protected]    4/19/2012

Say that a user has chosen row with Id 1 as the template row, and rows with Ids 2 and 3 are to be merged into row 1 and then deleted. Any null value columns in row Id 1 should be filled with (if one exists) the most recent (see Date column) non-null value, and non-null values already present in row Id 1 are to be left as is. The result of this query on the above data should be exactly this:

Id  Name     Address          City          State   Active  Email             Date
1   Acme1    1234 Abc Road    Springfield   OR      1       [email protected]     3/1/2011

Notice that the Active value is 1, and not 0 because row Id 3 had the most recent date.

P.S. Also, is there any way possible to do this without explicitly defining/knowing beforehand what all the column names are? The actual table I'm working with has a ton of columns, with new ones being added all the time. Is there a way to look up all the column names in the table, and then use that subquery or temptable to do the job?

like image 332
noahC Avatar asked Apr 19 '12 21:04

noahC


People also ask

How do I merge rows in Excel by criteria?

Combine rows in Excel with Merge Cells add-in To merge two or more rows into one, here's what you need to do: Select the range of cells where you want to merge rows. Go to the Ablebits Data tab > Merge group, click the Merge Cells arrow, and then click Merge Rows into One.

How do you combine values from multiple rows into a single row in Excel with a comma?

To combine the cell values from multiple rows, you just need to change the cell reference as this: =TEXTJOIN(",",TRUE,A2:A7) This TEXTJOIN also can combine a range of cells into a single cell with a delimiter as this: =TEXTJOIN(",",TRUE,A2:C7)

How do I merge adjacent rows with the same data in Excel?

Merge cellsClick the first cell and press Shift while you click the last cell in the range you want to merge. Important: Make sure only one of the cells in the range has data. Click Home > Merge & Center.


1 Answers

You might do it by ordering rows first by template flag, then by date desc. Template row should always be the last one. Each row is assigned a number in that order. Using max() we are finding fist occupied cell (in descending order of numbers). Then we select columns from rows matching those maximums.

; with rows as (
    select test.*,
  -- Template row must be last - how do you decide which one is template row?
  -- In this case template row is the one with id = 1
    row_number() over (order by case when id = 1 then 1 else 0 end,
                       date) rn
    from test
  -- Your list of rows to merge goes here
  -- where id in ( ... )
),
-- Finding first occupied row per column
positions as (
  select
    max (case when Name is not null then rn else 0 end) NamePosition,
    max (case when Address is not null then rn else 0 end) AddressPosition,
    max (case when City is not null then rn else 0 end) CityPosition,
    max (case when State is not null then rn else 0 end) StatePosition,
    max (case when Active is not null then rn else 0 end) ActivePosition,
    max (case when Email is not null then rn else 0 end) EmailPosition,
    max (case when Date is not null then rn else 0 end) DatePosition
  from rows
)
-- Finally join this columns in one row
select 
  (select Name from rows cross join Positions where rn = NamePosition) name,
  (select Address from rows cross join Positions where rn = AddressPosition) Address,
  (select City from rows cross join Positions where rn = CityPosition) City,
  (select State from rows cross join Positions where rn = StatePosition) State,
  (select Active from rows cross join Positions where rn = ActivePosition) Active,
  (select Email from rows cross join Positions where rn = EmailPosition) Email,
  (select Date from rows cross join Positions where rn = DatePosition) Date
from test
-- Any id will suffice, or even DISTINCT
where id = 1

You might check it at Sql Fiddle.

EDIT:

Cross joins in last section might actually be inner joins on rows.rn = xxxPosition. It works this way, but change to inner join would be an improvement.

like image 146
Nikola Markovinović Avatar answered Oct 03 '22 07:10

Nikola Markovinović