Browse DevX
Sign up for e-mail newsletters from DevX


X- and Y-Axis Transformations in SQL Server

Learn X-to-Y and Y-to-X axis transformation techniques for showing a table's row vertically or its column horizontally—no special operators or loops needed.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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.

Y-to-X or Vertical to Horizontal Transformation

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 pubs database yet, download it from the Microsoft Download Center.)

Begin by running the following query:

SELECT DISTINCT DATEPART(yyyy, hire_date) hire_year FROM pubs..employee

You will get the following result:

hire_year 1988 1989 1990 1991 1992 1993 1994

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 + ']' FROM (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

Thanks for your registration, follow us on our social networks to keep up-to-date