28 May 2020

MSSQL T-SQL Pivot Example

I’ve been using Microsoft SQL Server for a long time. Most things I do have become muscle memory and I don’t even think about syntax as I go. That is, except SQL Pivot and FOR XML to make an in-row csv. I use both of them every few weeks, but it’s just a weird syntax. Today we’re talking about pivot… Every time I look it up. Every time I scroll to the very last page of the microsoft technet guide on it. Today I’m going to write a quick example and explanation. Another day we’ll talk about dynamic pivot

How to Pivot - Pivot means take something that has values in rows and turns them into columns where the columns you are not pivoting match.

So imagine we’re trying to turn a list of ingredients for some burgers into a list of columns.. for reasons!

here’s an example of what that data would look like..

declare @t table  (
[Finished Item] varchar(MAX)
,[Ingredient] varchar(MAX)
,[Quantity] int
)
;
insert into @t
([Finished Item],[Ingredient],[Quantity])  VALUES 
 ('Cheese-Burger','Bun',1) 
, ('Cheese-Burger','Burger Patty',1) 
, ('Cheese-Burger','Cheese',1) 
, ('Cheese-Burger','Pickles',1) 
, ('Cheese-Burger','Sauce',1) 
, ('Double-Cheese-Burger','Bun',1) 
, ('Double-Cheese-Burger','Burger Patty',2) 
, ('Double-Cheese-Burger','Cheese',2) 
, ('Double-Cheese-Burger','Pickles',1) 
, ('Double-Cheese-Burger','Sauce',1) 
, ('Chicken-Burger','Bun',1) 
, ('Chicken-Burger','Chicken Fillet',2) 
, ('Chicken-Burger','Cheese',2) 
, ('Chicken-Burger','Sauce',1) 
, ('Chicken-Burger','Lettuce',1)

and the table data would look like this…

Finished Item	Ingredient	Quantity
Cheese-Burger	Bun	1
Cheese-Burger	Burger Patty	1
Cheese-Burger	Cheese	1
Cheese-Burger	Pickles	1
Cheese-Burger	Sauce	1
Double-Cheese-Burger	Bun	1
Double-Cheese-Burger	Burger Patty	2
Double-Cheese-Burger	Cheese	2
Double-Cheese-Burger	Pickles	1
Double-Cheese-Burger	Sauce	1
Chicken-Burger	Bun	1
Chicken-Burger	Chicken Fillet	2
Chicken-Burger	Cheese	2
Chicken-Burger	Sauce	1
Chicken-Burger	Lettuce

First thing we need to know is what the Unique Ingredient list is…

select distinct Ingredient from @t

and that returns..

Ingredient
Bun
Burger Patty
Cheese
Chicken Fillet
Lettuce
Pickles
Sauce

So.. to pivot.

	select
		*
	from @t t
	PIVOT (
		SUM(Quantity)
		FOR [Ingredient] 
		in ( [Bun],[Burger Patty],[Cheese],[Chicken Fillet],[Lettuce],[Pickles],[Sauce]	)
	) as pvt

and that creates

Finished Item	Bun	Burger Patty	Cheese	Chicken Fillet	Lettuce	Pickles	Sauce
Cheese-Burger	1	1	1	NULL	NULL	1	1
Chicken-Burger	1	NULL	2	2	1	NULL	1
Double-Cheese-Burger	1	2	2	NULL	NULL	1	1

to talk through this… it’s taking the Ingredient where it’s in the list (needs to be between [] ), and summing the Quantity for those, and putting it in a column with the name of the ingredient. it’s doing this for each [Finished Item] because that is NOT specified in the pivot. any column that is not specified works similar to being specified in a group-by for a pivot.

instead of SUM - you can min/max/avg/whatever (simple aggregates only)

items that do not have a value in the pivot will be NULL - you can use coalesce to make those zero if you wanted..

	select
		[Finished Item]
		,COALESCE([Bun],0) [Bun]
		 ,COALESCE([Burger Patty],0) [Burger Patty]
		 ,COALESCE([Cheese],0) [Cheese]
		 ,COALESCE([Chicken Fillet],0) [Chicken Fillet]
		 ,COALESCE([Lettuce],0) [Lettuce]
		 ,COALESCE([Pickles],0) [Pickles]
		 ,COALESCE([Sauce],0) [Sauce]
	from @t t
	PIVOT (
		SUM(Quantity)
		FOR [Ingredient] 
		in ( [Bun],[Burger Patty],[Cheese],[Chicken Fillet],[Lettuce],[Pickles],[Sauce]	)
	) as pvt

which creates

Finished Item	Bun	Burger Patty	Cheese	Chicken Fillet	Lettuce	Pickles	Sauce
Cheese-Burger	1	1	1	0	0	1	1
Chicken-Burger	1	0	2	2	1	0	1
Double-Cheese-Burger	1	2	2	0	0	1	1

That’s it!