By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,574 Members | 1,977 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,574 IT Pros & Developers. It's quick & easy.

How to convert row data into columns

P: 1
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.taxrate,t2.taxsumsys,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.linenum) 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
Dec 9 '09 #1
Share this Question
Share on Google+
1 Reply


nbiswas
100+
P: 149
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
Dec 9 '09 #2

Post your reply

Sign in to post your reply or Sign up for a free account.