Skip to content

Instantly share code, notes, and snippets.

@3leftturns

3leftturns/SQL pivot query

Created May 28, 2014 01:58
Show Gist options
  • Save 3leftturns/ade2555db8e44f4a7d69 to your computer and use it in GitHub Desktop.
Save 3leftturns/ade2555db8e44f4a7d69 to your computer and use it in GitHub Desktop.
Download ZIP
SQL collapse columns to rows. Got a crappy database that has sales data in columns by city tallied up in each column? This will collapse the column into 4 columns: saleDate, saleAmount, city, saleQuantity. See the alternate file for a visual.
Raw
SQL pivot query
SELECT salesTable.saleDate, salesTable.saleAmount, 'Reno' AS city, salesTable.reno AS saleQuantity
FROM salesTable
WHERE salesTable.reno > 0
UNION
SELECT salesTable.saleDate, salesTable.saleAmount, 'Salt Lake City' AS city, salesTable.saltLakeCity AS saleQuantity
FROM salesTable
WHERE salesTable.saltLakeCity > 0
UNION
SELECT salesTable.saleDate, salesTable.saleAmount, 'Las Vegas' AS city, salesTable.lasVegas AS saleQuantity
FROM salesTable
WHERE salesTable.lasVegas > 0
UNION
SELECT salesTable.saleDate, salesTable.saleAmount, 'Denver' AS city, salesTable.denver AS saleQuantity
FROM salesTable
WHERE salesTable.denver > 0
Raw
Table & Query Results
CRAPPY TABLE:
|PK|saleDate |saleAmount|reno |saltLakeCity|lasVegas|denver|
===============================================================
|01|2014-1-1 |53000.00 | 1 | NULL | NULL | NULL |
|02|2014-2-15 |47000.00 | NULL | 2 | NULL | NULL |
|03|2014-3-24 |68000.00 | NULL | NULL | NULL | 3 |
|04|2014-4-1 |72000.00 | NULL | NULL | 4 | NULL |
|05|2014-5-6 |27000.00 | 2 | NULL | NULL | NULL |
AWESOME NORMALIZED QUERY:
|saleDate |saleAmount|City | saleQuantity |
======================================================
|2014-01-01|53000.00 |Reno | 1 |
|2014-02-15|47000.00 |Salt Lake City | 2 |
|2014-03-24|68000.00 |Denver | 3 |
|2014-04-01|72000.00 |Las Vegas | 4 |
|2014-05-06|27000.00 |Reno | 2 |
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment