SQL query combine rows based on common id-CodePudding

I have a table with the below structure:

MID	FromCountry	FromState	FromCity	FromAddress	FromNumber	ToCountry	ToCity	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?

CodePudding user response：

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