Microsoft – SQL Server – Transact SQL – Using PIVOT and CTE Functions

Microsoft – SQL Server – Transact SQL – Using PIVOT and CTE Functions

Here is an interesting little sample on how to using PIVOT Statements ( in MS SQL Server – Transact SQL) :

Prepare and Populate Tables

Sample Query

use [tempdb]
go

create table Widget
(
	WidgetCode varchar(3),
	WSize int 
)

create table Gadget
(
	GadgetCode varchar(3),
	WidgetCode varchar(3),
	Num_Avail int,
	Color varchaR(21)
)

INSERT INTO Widget VALUES ( 'ABC',3)
INSERT INTO Widget VALUES ( 'DEF',2)
INSERT INTO Widget VALUES ( 'GHI',50)

INSERT INTO Gadget VALUES ( 1, 'ABC',5, 'Blue' )
INSERT INTO Gadget VALUES ( 2, 'ABC',2, 'White' )
INSERT INTO Gadget VALUES ( 3, 'DEF',2, 'White' )

Sample Query

Sample Query



WITH cte (Color, Num_Avail)
AS
-- Define the CTE query.
(
    SELECT Color, sum(Num_Avail) as Num_Avail
	from   Gadget
	group by Color
)	
select 
	  [Blue]  as [AvailableBlue]
	, [White] as [AvailableWhite]
	, [Blue] + [White] as [TotalAvailable]
from   cte
  	  pivot 
		(
	  	   sum(Num_Avail)
		   for Color in ([Blue], [White])
		)
		  as [SumNumAvail]

Code Repository

Code Repository – SQL Fiddle

have the code on SQLFiddle
URL is http://sqlfiddle.com/#!6/51ca1/1

SQLFiddle - Pivot Sample

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s