Combine 2 rows become 1 row

Question

I have data like this

MemberId | VesselName | Cargo | Date
B001     | Ship A     | 500   | 2013/11/12
B001     | Ship X     | 250   | 2013/11/20
B003     | Ship Y     | 300   | 2013/11/11

I want query result like this

MemberId | VesselName    | Cargo | Date
B001     | Ship A Ship X | 750   | 2013/11/12 2013/11/20
B003     | Ship Y        | 300   | 2013/11/11

Do you always have at most two rows that you want to combine? If this is the case, you can use a self join, as proposed by Dd2. If you can an arbitrary number of rows sharing the same memberid, you need to aggregate your data, which is a simple SUM for Cargo, but requires a GROUP_CONCAT for Date, which is not directly supported by SQL Server. There are workarounds, however, e.g., [here](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) — Fabian, Nov 12 '13 at 13:33
Why do you need your query to look like that? In general, people want to concat aggregate strings because they want their data more human-readable, which is a front end issue and should probably be handled in whatever receives the data, not in the SQL itself. — Tobberoth, Nov 12 '13 at 13:41

score 0 · Answer 1 · answered Nov 12 '13 at 13:40

The hard part would be to get the many VesselNames together...

If you have 2 ships maximum for each member, you could do something like:

select MemberId, 
       min(VesselName) + " " + max(VesselName) as Names, 
       sum(Cargo) as TotalCargo,
       cast(min(Date) as char(20)) + ' to ' + cast(max(Date) as char(20)) as Dates
from YourTable
group by MemberId

Combine 2 rows become 1 row

1 Answers1