title: How to create a table of name-value pairs in SQL
body: <p>Using SQL, how do I convert a single row table like this...</p>
  
  <pre><code>Firstname Surname Address1        City   Country
  --------- ------- --------------- ------ -------
  Bob       Smith   101 High Street London UK
  </code></pre>
  
  <p>...to a table of <strong>name-value</strong> pairs like this: </p>
  
  <pre><code>Name      Value
  --------- -------
  Firstname   Bob
  Surname     Smith
  Address1    101 High Street
  City        London
  Country     UK
  </code></pre>
  
  <p>This script will create the original table: </p>
  
  <pre><code>create table #OriginalTable (Firstname varchar(10), Surname varchar(10), 
  Address1 varchar(50), City varchar(10), Country varchar(10))
  insert into #OriginalTable 
  select 
  'Bob' Firstname, 
  'Smith' Surname, 
  '101 High Street' Address1, 
  'London' City, 
  'UK' Country
  </code></pre>
  
  <p>I'm after a generic solution that does not depend on the columns names always being what they are in the example.</p>
  
  <p>EDIT: 
  I'm using SQL Server 2005. 
  The solution I'm after is the SQL script to convert this data into a name-value pair table</p>
  
  <p>ANSWER: 
  Using the answer that I accepted as the answer, this is what I've used: </p>
  
  <pre><code>select   
  result.Name, 
  result.Value    
  from   
    (select
      convert(sql_variant,FirstName) AS FirstName,
      convert(sql_variant,Surname) AS Surname,
      convert(sql_variant,Address1) AS Address1,
      convert(sql_variant,City) AS City,
      convert(sql_variant,Country) AS Country
      from #OriginalTable) OriginalTable
    UNPIVOT (Value For Name In (Firstname, Surname, Address1, City, Country)) as result
  </code></pre>