Representation Multiple Results As One Record.

Hi,

Here is my sample table creation and insertion script.
I want represent as a summary-result using just one record.
How can I ?
Please note the below red color text.


create table policy
(
id int not null,
name nvarchar(50) not null,
constraint pk_policy primary key(id)
);


create table localhost
(
policy_id int not null,
id int not null,
ip_begin binary(4) not null,
ip_end binary(4) not null,
prefix tinyint not null default 0,
constraint pk_localhost primary key(policy_id,id)
);


create table remotehost
(
policy_id int not null,
id int not null,
ip_begin binary(4) not null,
ip_end binary(4) not null,
prefix tinyint not null default 0,
constraint pk_remotehost primary key(policy_id,id)
);


create table rate
(
policy_id int not null,
inbound int not null,
outbound int not null,
constraint pk_rate primary key(policy_id)
);



-------------
insert into policy values(0,N'policy0');

insert into localhost values(0,0,0xC0A80101,0xC0A80101,24);
insert into localhost values(0,1,0xC0A80A01,0xC0A80B01,0);

insert into remotehost values(0,0,0xACA80101,0xACA80101,0);
insert into remotehost values(0,1,0xACA80A01,0xACA80B01,0);
insert into remotehost values(0,2,0xACA80C01,0xACA80C01,24);

insert into rate values(0,1000,2000);

-------------
select * from policy;
select * from localhost;
select * from remotehost;
select * from rate;

-- result of policy table
0 policy0

-- result of localhost table
0 0 0xC0A80101 0xC0A80101 24
0 1 0xC0A80A01 0xC0A80B01 0

-- result of remotehost table
0 0 0xACA80101 0xACA80101 0
0 2 0xACA80C01 0xACA80C01 24

-- result of rate table
0 1000 2000

------------------------------------------------------
desired result set
id name localhost remotehost rate
-- ---------- ---------------------------------------------------------------- ------------------------------------------- -----------------
0 policy0 (192.168.1.1/24),(192.168.10.1~192.168.11.1) (172.168.1.1),(172.168.12.1/24) 0,1000,2000


descriptin of desired result set :
0. key value is id column and is referenced by each policy_id column.
1. id and name column is the same as policy table's.
2. localhost and remotehost columns are intigration of ip_begin,ip_end,prefix.
3. ip_begin and ip_end should be converted dotted presention from numeric format.
4. if prefix greater than 0, it should be displayed using '/'.
5. if ip_begin and ip_end are equal, show just one ip.
6. if the two ips are different from each other, they separated by '~'.
7. rate field is packed divided by ','.