SQL query combine rows based on common id

user_19506074 0
Viewed 56

I have a table with the below structure:

MID FromCountry FromState FromCity FromAddress FromNumber FromApartment ToCountry ToCity ToAddress ToNumber ToApartment
123 USA Texas Houston Well Street 1 Japan Tokyo 6 ET3
123 Germany Bremen Bremen Nice Street 4 Poland Warsaw 9 ET67
456 France Corsica Corsica Amz Street 3 Italy Milan 8 AEC784
456 UK UK London G Street 2 Portugal Lisbon 1 LP400

The desired outcome is:

MID FromCountry FromState FromCity FromAddress FromNumber FromApartment ToCountry ToCity ToAddress ToNumber ToApartment FromCountry1 FromState1 FromCity1 FromAddress1 FromNumber1 FromApartment1 ToCountry1 ToCity1 ToAddress1 ToNumber1 ToApartment1
123 USA Texas Houston Well Street 1 Japan Tokyo 6 ET3 Germany Bremen Bremen Nice Street 4 Poland Warsaw 9 ET67
456 France Corsica Corsica Amz Street 3 Italy Milan 8 AEC784 UK UK London G Street 2 Portugal Lisbon 1 LP400

What I am trying to achieve is to bring multiple rows in 1 table, which have the same MID, under 1 row, regardless if there are columns with empty values.

I think that i over complicated the solution to this as I was trying something like this (and of course the outcome is not the desired one):

select [MID],
    STUFF(
        (select concat('', [FromCountry]) 
        FROM test i
        where i.[MID] = o.[MID]
        for xml path ('')),1,1,'') as FromCountry
    ,stuff (
        (select concat('', [FromState]) 
        FROM test i
        where i.[MID] = o.[MID]
        for xml path ('')),1,1,'') as FromState
    ,stuff (
        (select concat('', [FromCity]) 
        FROM test i
        where i.[MID] = o.[MID]
        for xml path ('')),1,1,'') as FromCity
    ,stuff (
        (select concat('', [FromAddress]) 
        FROM test i
        where i.[MID] = o.[MID]
        for xml path ('')),1,1,'') as FromAddress
FROM test o
group by [MID] 
...

Is there any way to achieve this?

row sql-server sql
1 Answers

On the assumption there are no more than 2 rows per MID then you can implement a simple row_number() solution.

You need to join one row for each MID to the other, so assign a unique value to each using row_number - there's nothing I can immediately see that indicates which row should be the "second" row - this is assigning row numbers based on the FromCountry - amend as necessary.

I'm not reproducing all the columns here but you get the idea, rinse and repeat for each column.

with m as (
  select *, Row_Number() over(partition by Mid order by FromCountry) seq
  from t
)
select m.Mid,
  m.fromcountry, m.fromstate, 
  m2.fromcountry FromCountry1, m2.fromstate FromState1
from m 
join m m2 on m.mid = m2.mid and m2.seq = 2
where m.seq = 1;

See example fiddle

user_153326500
Related