How do I group different rows in a field?

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 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.

• Answer:

  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