Jeff Smith Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server

UNPIVOT: Normalizing data on the fly

Everyone seems to want to "pivot" or "cross tab" data, but knowing how to do the opposite is equally important. In fact, I would argue that the skill of "unpivoting" data is more useful and more important and more relevant to a SQL programmer, since pivoting results in denormalized data, while unpivoting can transform non-normalized data into a normalized result set. Read more →

Keep those SQL Crosstabs flexible!

I recently helped someone with performing some simple cross tabs (or pivots) in SQL, such as: Select CustomerID, SUM(CASE WHEN type = 'Land' THEN Amount ELSE 0 END) as Land, SUM(CASE WHEN type = 'Sea' THEN Amount ELSE 0 END) as Sea, SUM(CASE WHEN type = 'Air' THEN Amount ELSE 0 END) as Air FROM SomeTable GROUP BY CustomerID Read more →