A very long time ago, my first introduction to SQL was through the SQL view in MS Access. I would build a query and then look at the SQL view to see how it translated that into SQL. Using MS Access gave me a decent foundation in how to build queries and connect tables.
Fast forward about 20 years and Tableau Prep is now my go to tool to help people learn how to manipulate and clean data. The built in functionality in Prep makes it a breeze to get your data in the format you want, but, there is no SQL view in Prep to look at and learn from. This is the first in a series of posts on how you would replicate steps from Prep in SQL.
For this example I’ve connected to the superstore data set and build a simple flow that counts the number of orders by ship mode and segment. In Prep we would do this by adding an aggregate step (the sigma step) that counts the orders by segment and ship mode.
The next step is to pivot (or reshape the data). In this step I am moving the ship mode to the columns so this is a rows to columns pivot step.
This is how to achieve the same result by using SQL, First create a query that pulls the fields in from the Superstore data set.
SELECT (select is saying pick these fields)
S.Segment, (separate your selected fields with a comma)
S.[Ship Mode], (use  when your field name has a space)
FROM SUPERSTORE S (from is where you set the data source. The “S” after the table name is an alias. I think it is a good practice to use alias, especially when you start joining tables)
This gets the initial data set that we want. Now we want to add on to this query to move the Ship Mode to the columns and count the order ids.
SELECT * FROM (this is new and we add this to select all of the fields from the statement)
(SELECT (this is the query from above)
FROM SUPERSTORE S) O
PIVOT( (this starts the reshaping of the data)
COUNT(O.[Order ID]) (we want to get a count of the order id)
FOR O.[Ship Mode] IN (this specifies the values that we want to include in our pivot. These are the values in the ship mode field. you can enter all or just a few of the values)
) AS ORDERS_SHIPMODE
Here is the query without my notes:
SELECT * FROM
FROM SUPERSTORE.dbo.Orders$ S) O
FOR O.[Ship Mode] IN
) AS ORDERS_SHIPMODE
Please note this is based on SQL for MS SQL Server. Oracle has a PIVOT function as well but the syntax is different. If you are copying the above query be sure to remove my comments in the () in the statement.
Hope this helps you understand how to do a simple pivot in SQL.