1

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.

ArtOx
  • 11
  • 2

1 Answers1

4

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

Community
  • 1
  • 1
Andrew Bezzub
  • 15,744
  • 7
  • 51
  • 73
  • UNPIVOT works, but I'm getting this message (below) even though all my fields are varchar. – ArtOx Oct 25 '10 at 16:05
  • The type of column "" conflicts with the type of other columns specified in the UNPIVOT list. – ArtOx Oct 25 '10 at 16:29
  • Can you please add query you have to the question? – Andrew Bezzub Oct 25 '10 at 17:41
  • SELECT Id, FieldName, FieldValue FROM (SELECT * FROM dbo.People WHERE Id = 1) p UNPIVOT (FieldValue FOR FieldName IN (Id, FirstName, LastName, DOB)) AS u ORDER BY FieldName – ArtOx Oct 25 '10 at 19:10
  • @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. – Jeff Ogata Oct 26 '10 at 11:45