To be blunt. Don't.
That's formatting for display, not data retrieval (unless Address3 has no user, which is what your example implies).
If you want to display like that, look at the settings and properties of whatever you're using as a front end. Reporting Services for example will let you chose the names as a grouping level and the addresses as details.
In SQL however, the rows should be independant (especially since there's no order by or anything suggesting order in your example) and so your example is equivalent to
NULL NULL Address2
NULL NULL Address3
NULL NULL Address5
NULL NULL Address6
1 name1 Address1
2 name2 Address4
3 name3 NULL
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass