Systems Engineering and RDBMS

A very simple way of pivoting row to column data in SQL Server

Posted by decipherinfosys on March 26, 2007

We had covered pivoting and un-pivoting of data in one of our previous post related to our whitepaper on the subject.  Here is yet another very simple way of doing pivoting if you need to get the data in a variable.

declare @str varchar(8000)
set @str = ”
select @str = @str + ‘, ‘ + name
from sysobjects
where xtype = ‘u’
order by name
print substring(@str, 3, len(@str))

This will prepare a comma separated list of all the tables in the schema.  If your list is huge, you can replace varchar(8000) with varchar(max) in SQL Server 2005.  This technique can be used in different places within the application code as well and works in SQL Server 2000 as well as SQL Server 2005.

Sorry, the comment form is closed at this time.

 
%d bloggers like this: