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