Using SQL Server’s PIVOT Operator

Kevin Feasel

2018-03-26

T-SQL

Dan Blank shows how to use the PIVOT operator in SQL Server:

I was recently approached by my firms Marketing Manager with a request for some information.  She wanted to know “Which departments have our top clients never done any work for?”.  For some clarity, I work in a law firm with 11 departments. The request seems pretty straightforward at first. Then once I got thinking about how the output of this report would be presented it made me reconsider quite how simple a request this was.  She handed me a drawing with her vision for the output.  What she wanted was :

  1. Client’s names down the left,

  2. List of Departments across the top,

  3. Ticks and crosses at the intersection to show whether they had or had not done work for them.

You can make a good argument that presentation mechanics like this are meant for a different tool (a presentation layer) but it’s useful to know how to pivot and unpivot data within T-SQL for more reasons than just presentation.

Related Posts

APPROX_COUNT_DISTINCT

Niko Neugebauer is happy with a new function in SQL Server 2019: A rather interesting result takes place if we scale our database to 100GB TPCH and run the very same queries – the total elapsed time jumps to 50% difference (from 30%), the CPU execution time difference is kept at 50%, but the memory […]

Read More

Simulating LAG And LEAD Prior To SQL Server 2012

Izik Ben-Gan highlights a reader submission from his last post: Last month I covered a Special Islands challenge. The task was to identify periods of activity for each service ID, tolerating a gap of up to an input number of seconds (@allowedgap). The caveat was that the solution had to be pre-2012 compatible, so you couldn’t […]

Read More

Categories

March 2018
MTWTFSS
« Feb Apr »
 1234
567891011
12131415161718
19202122232425
262728293031