This post has already been read 1129 times!

We have a table with following format

Date 	CA 	  CB
==============
10/11/16 	100 	 200
11/11/16 	101 	 201
12/11/16 	102  202
13/11/16 	103 	203

Transformed table 

Column 	Date       Value
===================
CA 	      10/11/16 	100
CA 	      11/11/16 	101
CA 	      12/11/16 	102
CA 	      13/11/16 	103
CB 	      10/11/16 	200
CB 	       11/11/16 	201
CB 	       12/11/16 	202
CB 	       13/11/16 	203
; WITH SR AS (
	SELECT ts as Date , ca , cb
	FROM (SELECT Date , ca , cb FROM apivoter) p  
	UNPIVOT (ts FOR apivoter IN (Date) ) AS unpvt
)
select Nom_col = 'CA' , Date , ca as valeur from SR
UNION select Nom_col = 'CB' , Date , cb as valeur from SR

Simple Way To Use Pivot In SQL Query

This post intends to help T-SQL developers 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 in the past.

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 

selectallquery

 

 

 

 

 

 

 

 

 

 

 

 

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

pivote

Leave a Reply

Post Navigation