SQL Table Row Convert to List of Columns

views:

answers:

+1 Q:

SQL Table Row Convert to List of Columns

What is the best way to convert a row with columns to a list with rows? In the list, I need one column with the old column name and one column with the old column value.

For Example:

Current:

PersonId | PersonFirstName | PersonLastName |

Need:

ColumnName | ColumnValue

Hope this makes sense. Thanks for any help.

+2 A:

You need to use Pivot clause. You can see this question for some examples.

Andrew Bezzub 2010-10-25 15:51:47

UNPIVOT works, but I'm getting this message (below) even though all my fields are varchar.

ArtOx 2010-10-25 16:05:09

The type of column "" conflicts with the type of other columns specified in the UNPIVOT list.

ArtOx 2010-10-25 16:29:10

Can you please add query you have to the question?

Andrew Bezzub 2010-10-25 17:41:55

SELECT Id, FieldName, FieldValueFROM(SELECT * FROM dbo.People WHERE Id = 1) pUNPIVOT(FieldValue FOR FieldName IN (Id, FirstName, LastName, DOB)) AS uORDER BY FieldName

ArtOx 2010-10-25 19:10:35

@ArtOx, if the fields are different lengths, you will still get that type conflict error. Try casting all of the fields to a common size.

adrift 2010-10-26 11:45:28