I've come across this question quite a few times.

I have rows with lots of columns, and would like to split it out so that each column is a row

Imagine a scenario where you have a wide table that you'd like to normalize, at least in view or façade (or perhaps in a data transformation layer) - You'd like to un-pivot it, rotating columns into rows.

For a sample, I'll use the questioner's scenario of a Person table with a variety of attributes. Don't get hung-up on the table or whether it's already normalized (the sample came directly from a user who had a rather unique need), the point is simply rotating columns into rows.

You'd like to return a set like the following:

(rows truncated for some brevity)

SQL Server 2005 offers the UNPIVOT operator of the FROM clause, which can made quick (albeit unintuitively and inflexibly) work of this specific need.

SELECT   
  ID, tblPivot.Property, tblPivot.Value 
FROM   
  (SELECT ID,
     CONVERT(sql_variant,FirstName) AS FirstName,
     CONVERT(sql_variant,LastName) AS LastName,
     CONVERT(sql_variant,Age) AS Age
   FROM Person) Person
  UNPIVOT (Value For Property In (FirstName, LastName, Age)) as tblPivot

The use of the derived table in the FROM clause is purely to cast the columns to a common data type, as this is a requirement of the UNPIVOT operator. Otherwise all of the source columns would need to have the same type (precisely the same type).

Note that the IN list has to be a literal, concrete list - you can't pass a table variable or subquery. Always perplexing when they limit these sorts of operators in this way.

With SQL Server 2000 you can do this via simple unions or temporary tables

SELECT 
 ID, 'FirstName' AS [Property], CONVERT(sql_variant, FirstName) AS [Value] 
FROM 
 Person
WHERE
 FirstName IS NOT NULL

UNION ALL

SELECT 
 ID, 'LastName', CONVERT(sql_variant, LastName) 
FROM 
 Person
WHERE
 LastName IS NOT NULL

UNION ALL
SELECT 
 ID, 'Age', CONVERT(sql_variant, Age) 
FROM 
 Person
WHERE
 Age IS NOT NULL

In this case we've made the common type sql_variant, though obviously you should alter according to your data. We've also decided to eliminate null values (so there aren't null property rows), though that depends upon the need.

Another option is to make a programmatically flexible alternative that automatically adapts to the schema of the table (within constraints). For instance consider the following script.

SET NOCOUNT ON

DECLARE @table sysname 
SET @table = 'Person'

DECLARE @id_field sysname 
SET @id_field = 'ID'

DECLARE @sql varchar(8000)

-- create the schema of the resulting table 
SET @sql = 'SELECT TOP 0 CONVERT(int,0) AS [ID], '
 +'CAST(0 AS nvarchar(4000)) AS [Property],'
 +' CONVERT(sql_variant,N'''') AS [Value] WHERE 1=0 '+CHAR(10)

SELECT @sql = @sql + 'UNION ALL SELECT '+@id_field+', N'''
 +COLUMN_NAME+''',CONVERT(sql_variant, '
  +'['+COLUMN_NAME+']) FROM ['+@table+'] WHERE ['+COLUMN_NAME+'] IS NOT NULL '
  +CHAR(10)
FROM 
  INFORMATION_SCHEMA.COLUMNS 
WHERE 
  TABLE_NAME = @table 
  AND COLUMN_NAME <> @id_field 
ORDER BY COLUMN_NAME

IF (LEN(@sql) >0) 
BEGIN 
 EXEC(@sql) 
END

In this case it uses the object schema, though you could alter it to go against a property table or the like.

Standard disclaimers about injection attacks and all of that apply (presumably you won't be calling this with untrusted input), and of course it won't work if you have composite keys, or if you have so many columns that the resulting SQL exceeds 8000 characters. Adapt accordingly. It also does no sorting, so add as you need it.