Pivoting / Cross-Tabbing Data in SQL Server
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.
Concatenating Strings in Teradata – And What to Watch Out for with Fixed Length Fields
Concatenating Strings in Teradata is fairly straightforward, but there are a few things you need to watch out for.
Populate a table by looping through items in a list / another table
Quite often, when writing queries, we want to load a table with a condition we want to pass to it (rather than hard-coding the value in the WHERE clause). Eg, load sales for a given year.
This can be easily achieved by creating a Macro, and passing a value to it…
Adding shortcuts to your Teradata SQL Assistant Query Tabs
Move between tabs in Teradata’s SQL Assistant by assigning Short-Cuts to them
Using RANK(), and ROW_NUMBER to Rank a field without ‘skipping’ numbers in SQL Server & Teradata
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 in
Changing Your Teradata Password
Nice simple one today. The first thing you should ever do when granted access to any system, is change your password! Afterall, your IT policy probably a) requires you to and b) will hold you accountable for any misuse under your ID! This is simple in Teradata – you log in using your existing password,
Moving Data from Teradata to SQL Server
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,
Finding the Difference Between Two Dates in SQL
In this post, I’m going to talk about finding the difference between 2 dates in SQL. However, I’m going to cover it across multiple applications. The Problem Statement is simple: How many days are there between two dates? What we want is an integer value back of how many days there are. The solution will
Most Read Articles
