hen I was writing code for user interfaces, I needed to present data in different ways. Logically it wasn't difficult, but since the presentation layout was often quite different from the storage (tables') structure, I had to use numerous record sets, loops, and arrays to manipulate the X and Y axes of tables for display. This approach required a lot of code, made debugging more complicated, and bored me to death. So I came up with a simple back-end solution that allows fast and easy axes transformation. This article demonstrates my Y-to-X and X-to-Y transformation techniques.
The examples in this article use the employee table from the pubs sample database for SQL Server. If you are using SQL Server 2005 (SS2005) and didn't install the Microsoft Download Center.)
Begin by running the following query:
SELECT DISTINCT DATEPART(yyyy, hire_date) hire_year
You will get the following result:
Assume that you need to present the hire year horizontally, as follows:
hire_year 1988 1989 1990 1991 1992 1993 1994
You can do that pretty easily using the following script:
1 DECLARE @str nvarchar(100)
2 SELECT @str = 'hire_year'
3 SELECT @str = @str + CHAR(9) + hire_year FROM
4 (SELECT DISTINCT CAST(DATEPART(yyyy, hire_date)
5 AS varchar(10)) hire_year
6 FROM pubs..employee) T1
7 SELECT @str
The whole trick is in line 3, where
@str accumulates data, appending the new values to the previous ones. The source of the values is a specified column, in this case the hire_date column. Char(9) serves as a delimiter (I selected TAB (ASCII code 9), but you can choose whatever you want: comma, expression, and so on).
Using the same technique, you can create a table where the values of the original column hire_date will represent the columns' names:
DECLARE @str nvarchar(1000)
SELECT @str = ''
SELECT @str = @str + ',' + hire_year + ' [' + hire_year + ']'
(SELECT DISTINCT CAST(datepart(yy, hire_date)AS varchar(10)) as hire_year
FROM pubs..employee ) T1
-- delete the very first comma
SELECT @str = SUBSTRING(@str,2,len(@str) - 1)
SELECT @str = 'SELECT ' + @str + ' INTO hire_year FROM employee WHERE 1=0'
EXECUTE sp_executesql @str
SELECT * FROM hire_year
DROP TABLE hire_year