Browse DevX
Sign up for e-mail newsletters from DevX


X- and Y-Axis Transformations in SQL Server : Page 3

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

X-to-Y or Horizontal to Vertical Transformation

This section demonstrates using the X-to-Y transformation technique to pass many parameters to a stored procedure. As you know, passing a few parameters to a stored procedure is not a difficult task, but passing tens of parameters will produce many lines of additional code in the body of the stored procedure. If you go as far as passing hundreds of parameters with the requisite validation and processing (both SQL Server 2000 and 2005 allow a maximum of 2,100 parameters), it may become a non-trivial task.

One of the common ways to pass many parameters to stored procedures is collecting them into a long string and then separating them by delimiters. This is especially convenient when the number of parameters is dynamic. However, that approach may produce a problem: string processing is not the strongest feature in any RDBMS. How do you deal with such a situation? Well, you can try transforming a string into the table layout, which will make the validation and processing much easier. The following listing shows this technique using X-to-Y transformation:

SET QUOTED_IDENTIFIER OFF GO IF EXISTS (SELECT name FROM sysobjects WHERE name = 'spu_XtoYtransform' AND type ='P') DROP PROCEDURE spu_XtoYtransform GO CREATE PROCEDURE spu_XtoYtransform @iParam varchar(1000) = '', @sParam varchar(1000) = '', @del varchar(10) = '/' AS SET NOCOUNT ON -- X-to-Y transformation DECLARE @iStr nvarchar(4000), @sStr nvarchar(4000) CREATE TABLE #ip(ipID int IDENTITY(1,1) not null, ip int null) SELECT @iStr = 'INSERT INTO #ip(ip) SELECT A=' + REPLACE(@iParam,@del,' UNION ALL SELECT ') CREATE TABLE #sp(spID int IDENTITY(1,1) not null, sp VARCHAR(30) NULL) SELECT @sStr = 'INSERT INTO #sp(sp) SELECT A = "' + REPLACE(@sParam,@del,'" UNION ALL SELECT "') + '"' EXECUTE(@iStr + @sStr) SELECT * FROM #ip; SELECT * FROM #sp -- validation -- . . . . . . . . . . . . . . . . . . . . . . -- logic implementation -- . . . . . . . . . . . . . . . . . . . . . . GO -- How to run example EXEC spu_XtoYtransform '12/23/100/34/45/56/67', 'London/Paris/New-York/Toronto/Mexico/Lima/Tokyo','/'

Note that spu_XtoYtransform receives two string parameters: one consists of integers and the other of alphabet characters.

To transform each string into the vertical table view, you need to replace the delimiters by using the phrase "UNION ALL SELECT" and dynamically executing that statement. By using "UNION ALL" instead of just "UNION", you'll get the values in the resulting column in exactly the same order as they were in the string.

The following listing provides the code for showing a table's row vertically as a column:

1 USE pubs 2 GO 3 DECLARE @str nvarchar(4000) 4 SELECT @str = '' 5 SELECT @str = @str + ','+ COLUMN_NAME + 6 ' FROM pubs..employee WHERE emp_id=' + '''' + 'PMA42628M' + '''' 7 FROM pubs.INFORMATION_SCHEMA.COLUMNS 8 WHERE TABLE_NAME = 'employee' 9 SELECT @str = 'INSERT INTO #XtoY SELECT a=' + 10 SUBSTRING(@str,2,len(@str) - 1) 11 CREATE TABLE #XtoY(X_values varchar(30) null) 12 SELECT @Str = REPLACE(@str,',',' UNION ALL SELECT ') 13 EXECUTE sp_executesql @str 14 SELECT * FROM pubs..employee WHERE emp_id='PMA42628M' 15 SELECT * FROM #XtoY 16 DROP TABLE #XtoY

You can enumerate the columns manually or apply Y-to-X transformation to the INFORMATION_SCHEMA.COLUMNS view as the example does. Then you just need to replace the comma delimiter using "UNION ALL SELECT" and execute the final statement.

Note: Code in the previous listing works in SQL Server 2000. To make it work in SS2005, you must explicitly convert each column into varchar, replacing line 5 with the following:

SELECT @str = @str + ',CAST('+ COLUMN_NAME + ' AS varchar(60))' +

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