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.Na…

Accepted Answer

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
    )
```

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

Solution

Problem

Related problems