I was having a discussion with someone the other day on the merits of using PIVOT / Cross-Tab functionality in SQL.
Back when the primary platform I used was Teradata, you didn’t have a PIVOT function, so if you wanted to do something similar to a Pivot, you had to build a series of CASE statements to build your fields up. This was fine, but you had to know beforehand what columns you wanted to have on your output
SQL SERVER (2005+) does provide a PIVOT option, but to be honest, it’s only marginally better than the CASE statement approach – you still have to know what column headings you want in your query.
So, if you want to pivot by the day of the week, that’s fine because there are a fixed number of days in the week and you can account for this.
But what if you want to have a field for each Agent/Region/date etc? You won’t always be able know what fields you’re going to need.Read More
In this post, I want to cover off a little bit of RANK() usage in SQL, but then focus on how to use ranking to get a unique rank number for each row. I will cover both SQL Server and Teradata. RANK() is pretty simple to use, and works pretty much the same way inRead More
Update – December 2016 I wrote this article back in 2013, based on my current experiences working between SQL Server & Teradata. With the benefit of time (and learning more!), there are probably better ways I could have achieved what I’ve outlined below. Most notably using Integration Services. That said, it was a good learning experience,Read More