我有一张 struct 如下的桌子:
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 |
预期结果是:
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 |
我试图实现的是将一个表中具有相同MID的多行放在一行下,而不管是否有具有空值的列.
我认为我在try 这样的事情时把解决方案复杂化了(当然结果不是预期的):
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]
...
有什么方法可以做到这一点吗?