SQL

Pivot Table (From Long to Wide)

In: SQL

Every data scientist, scientist, and engineer should know how to create a pivot table.

π™²π™°πš‚π™΄ πš†π™·π™΄π™½ is the best way to do so in SQL.

πŸš€ Here are THREE ways to pivot a table for monthly sales.
1️⃣ We find the πš‚πš„π™Ό of all salesperson each month. Here, use πš‚πš„π™Ό(π™²π™°πš‚π™΄ πš†π™·π™΄π™½).
2️⃣ We find the average sales of each salesperson each month? Use π™Άπšπ™Ύπš„π™Ώ π™±πšˆ + π™°πš…π™Ά(π™²π™°πš‚π™΄ πš†π™·π™΄π™½)
3️⃣ Identical to example 1, but using PostgreSQL's FILTER clause.

Some other DBMS supports fancy functions for pivot table. For example, Redshift supports the PIVOT function.

Not sure what DBMS you're using? CASE WHEN works for all cases and is always your safest bet.

πŸ‘‡ Full example below.

More from Travis Tang
Use ARRAY_AGG to flatten columns to lists.
SQL

Use ARRAY_AGG to flatten columns to lists.

You need to convert a long table of values into a list? This is the most convenient function. πŸ“Œ Syntax: πš‚π™΄π™»π™΄π™²πšƒ π™°πšπšπ™°πšˆ_𝙰𝙢𝙢(πšŒπš˜πš•πšžπš–πš—) π™΅πšπ™Ύπ™Ό πšπšŠπš‹πš•πšŽ There
Find duplicates with RANK
SQL

Find duplicates with RANK

Let's clean out duplicates! Here's how. 1️⃣ Check if duplicates exists. Use π™Άπšπ™Ύπš„π™Ώ π™±πšˆ + π™²π™Ύπš„π™½πšƒ(*) to find duplicates. See example 1 in image
Great! You’ve successfully signed up.
Welcome back! You've successfully signed in.
You've successfully subscribed to Travis Tang.
Your link has expired.
Success! Check your email for magic link to sign-in.
Success! Your billing info has been updated.
Your billing was not updated.