How to convert row data into columns

[Aarati]

Hello Sir, when I run following query in SQL Server,

"
SELECT T0.[Dscription], t1.docdate as 'Invdate' ,STR(T1.Serial) AS 'Invoice No', T0.[Quantity], T0.[PriceBefDi],T0.LineTotal, t2.stacode,t2.t axrate,t2.taxsu msys,T0.GTotal, T4.[Location] FROM [dbo].[INV1] T0 INNER JOIN [dbo].[OINV] T1 ON T1.DocEntry = T0.DocEntry inner join inv4 t2 on (t2.docentry= t0.docentry and t2.linenum=t0.l inenum) INNER JOIN OLCT T4 ON T0.LocCode = T4.Code INNER JOIN [dbo].[OITM] T3 on t0.itemcode=t3. itemcode where T1.Docdate>=[%1] and T1.Docdate<=[%2] and T0.[Dscription]=[%0]
"

I get following result

"
Item Name InvDate Invoice No Quantity unit price Rowtotal AuthCodeTaxRate
A 5/12/09 12634 15 20.75 311.25 BED @8 8
A 5/12/09 12634 15 20.75 311.25 HSC 1
A 5/12/09 12634 15 20.75 311.25 eces 2
A 5/12/09 12634 15 20.75 311.25 VAT 12.5
"
I want above result in following way

"
Item InvDate Invoice No Quty price Rowtotal BED @8 HSC eces VAT
A 5/12/09 12634 15 20.75 311.25 8 2 1 12.5
"

Please guide on this

Regards

Aarati Kollur

[nbiswas]

This kind of problems are called Cross Tab Queries and can be solved easily using PIVOTING (a feature from SQL SERVER 2005+)

Have a look

Pivot Table Data in SQL Server 2000 and 2005

Hope this helps