How to create a table of name-value pairs in SQL

Question

Using SQL, how do I convert a single row table like this...

Firstname Surname Address1        City   Country
--------- ------- --------------- ------ -------
Bob       Smith   101 High Street London UK

...to a table of name-value pairs like this:

Name      Value
--------- -------
Firstname   Bob
Surname     Smith
Address1    101 High Street
City        London
Country     UK

This script will create the original table:

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

I'm after a generic solution that does not depend on the columns names always being what they are in the example.

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

ANSWER: Using the answer that I accepted as the answer, this is what I've used:

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

Answer 1

This sounds like the kind of things the PIVOT clause can do in SQL Server since 2005 (look for the first example), but you don't mention which database engine you use.

Answer 2

Not that it be a complete solution, but is a brainstorm idea, what if you cross join information_schema.columns with your table?

SELECT column_name, OriginalTable.*
FROM information_schema.columns 
CROSS JOIN OriginalTable
WHERE table_name = 'OriginalTable'
AND /* PRIMARY KEY FILTER HERE*/

Answer 3

Often it is most effective to pivot in the application using application code. Pivoting does not tend to be a database's strong point.

Answer 4

See this answer to a similar question: http://stackoverflow.com/questions/651936/how-can-i-improve-this-mailing-address-sql-server-select-statement/652065#652065

Answer 5

Use two tables. One table as a table of 'keys' and the main table contains an id to the keys table, together with a value.

Then, you can add stuff like client_id or whatever to the main table as well and set a unique key on client_id and key_id.

Answer 6

Basically you have two problems - to UNPIVOT, the data types have to be conformed. The other problem is that the number of columns is unknown. You want to reach something of the form:

WITH    conformed
      AS ( SELECT   CONVERT(VARCHAR(255), [Firstname]) AS [Firstname],
                    CONVERT(VARCHAR(255), [Surname]) AS [Surname],
                    CONVERT(VARCHAR(255), [Address1]) AS [Address1],
                    CONVERT(VARCHAR(255), [City]) AS [City],
                    CONVERT(VARCHAR(255), [Country]) AS [Country]
           FROM     so1526080
         )
SELECT  ColumnKey,
        ColumnValue
FROM    conformed UNPIVOT ( ColumnValue FOR ColumnKey IN ( [Firstname], [Surname], [Address1], [City], [Country] ) ) AS unpvt

So using a dynamic SQL PIVOT using metadata (you might need to fix this up with TABLE_SCHEMA, etc):

DECLARE @table_name AS SYSNAME
SET @table_name = 'so1526080'
DECLARE @conform_data_type AS VARCHAR(25)
SET @conform_data_type = 'VARCHAR(255)'

DECLARE @column_list AS VARCHAR(MAX)
DECLARE @conform_list AS VARCHAR(MAX)

SELECT  @conform_list = COALESCE(@conform_list + ', ', '') + 'CONVERT('
        + @conform_data_type + ', ' + QUOTENAME(COLUMN_NAME) + ') AS '
        + QUOTENAME(COLUMN_NAME),
        @column_list = COALESCE(@column_list + ', ', '')
        + QUOTENAME(COLUMN_NAME)
FROM    INFORMATION_SCHEMA.COLUMNS
WHERE   TABLE_NAME = @table_name

DECLARE @template AS VARCHAR(MAX)

SET @template = '
WITH    conformed
          AS ( SELECT  {@conform_list}
               FROM     {@table_name}
             )
    SELECT  ColumnKey,
            ColumnValue
    FROM    conformed UNPIVOT ( ColumnValue FOR ColumnKey IN ( {@column_list} ) ) AS unpvt
    '

DECLARE @sql AS VARCHAR(MAX)
SET @sql = REPLACE(REPLACE(REPLACE(@template, '{@conform_list}', @conform_list),
                           '{@column_list}', @column_list), '{@table_name}',
                   @table_name)    

PRINT @sql
EXEC ( @sql
    )