SQL to group data in mutiple rows in a single field

I have a table BillData like this:

Column   Data Type
BilNo      INT
SlNo       INT
Rate       FLOAT
Weight     FLOAT
Type       VARCHAR(2)

There is sample data like this:

BilNo SlNo   Rate    Weight Type
10001   1   1000.00 3.231   GM
10001   2   1200.00 2.354   GM
10001   3   1300.00 1.891   CT
10002   1   900.00  5.458   GM
10003   1   1450.00 9.520   GM
10003   2   1100.00 6.352   GM

I need to club the weight and rates into a single column (Rate Weight ) and group according to BilNo and present a report like this:

Bill No Data                                              Type
10001   1000.00  3.231   开发者_如何学JAVA 1200.00 2.354    1300.00 1.891    GM GM CT
10002   900.00 4.454                                        GM
10003   1450.00 9.520    1100 6.352                         GM GM

Please help me to achieve this.

---

You can group by on BilNo and use a sub query with for xml to concatenate Rate, Weight and Type.

select 
  B.BilNo as [Bill No],
  (select cast(Rate as varchar(10))+' '+cast([Weight] as varchar(10))+'  '
     from BillData as B2
     where B.BilNo = B2.BilNo
     for xml path(''), type).value('.[1]', 'varchar(max)') as Data,
  (select [Type]+' '
     from BillData as B2
     where B.BilNo = B2.BilNo
     for xml path(''), type).value('.[1]', 'varchar(max)') as [Type]
from BillData as B
group by B.BilNo

---

You can use the GROUP_CONCAT function which works with the GROUP BY command.

SELECT BillNo, 
 GROUP_CONCAT(Rate, ' ', Weight SEPARATOR '    ') AS DATA, 
 GROUP_CONCAT(TYPE SEPARATOR ' ') AS TYPE
FROM BillData
GROUP BY BillNo;

Edit
This works nicely for me in MySQL 5.1.53. You didn't specify so maybe you're using a different database engine. Here's the table creation code. You can cut and past it, as well as the sql syntax above:

CREATE TABLE BillData (
  BillNo int(11) DEFAULT NULL,
  SlNo int(11) DEFAULT NULL,
  Rate float DEFAULT NULL,
  Weight float DEFAULT NULL,
  Type varchar(2) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

LOCK TABLES BillData WRITE;
/*!40000 ALTER TABLE BillData DISABLE KEYS /;
INSERT INTO BillData (BillNo,SlNo,Rate,Weight,Type)
VALUES
    (10001,1,1000,3.321,'GM'),
    (10001,2,1200,2.354,'GM'),
    (10001,3,1300,1.891,'CT'),
    (10002,1,900,5.458,'GM'),
    (10003,1,1450,9.52,'GM'),
    (10003,2,1100,6.352,'GM');

/!40000 ALTER TABLE BillData ENABLE KEYS */;
UNLOCK TABLES;

And I get these results from the query:

10001   1000    3.321   1200 2.354    1300  1.891   GM GM CT
10002   900 5.458                                   GM
10003   1450    9.52    1100 6.352                  GM GM