2 Replies Latest reply: Jan 30, 2018 8:58 AM by Andrea Gigliotti RSS

    howto: pivot table using sql

    fabio Mazzarelli

      Hi Community,

      may you help to get started with PIVOT queries. Most business applications will need some sort of PIVOT queries and I am sure many of you must have come across pivoting requirements several times.

      Using the Code

      Let us have a table name Invoice which has three properties, InvoiceNumber, InvoiceDate, InvoiceAmount. Suppose we have several rows input in the table. Our goal is to display the sum of InvoiceAmount each month.

      SELECT * FROM (SELECT year(invoiceDate) as [year], left(datename(month,invoicedate),3)as [month], _ InvoiceAmount as Amount FROM Invoice) as InvoiceResult 

      SELECT * FROM ( SELECT year(invoiceDate) as [year],left(datename(month,invoicedate),3)as [month], InvoiceAmount as Amount 
      FROM Invoice ) as s PIVOT ( SUM(Amount) FOR [month] IN (jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec) )
      AS pvt