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

What DBMS are you using? Different DBMS store the names of the columns differently. — Ben S, Oct 06 '09 at 14:54
So you'd have multiple rows for each key? e.g. if the original table didn't just have a row for Bob Smith, but also for John Doe, then your name-value pair table would have 2 rows for each key. — Dominic Rodger, Oct 06 '09 at 14:54
@Dominic. No, the original table will always only have a single row. If there are more rows in the database for different people, the original table would still just have a single row, b/c it is just a filter for a single person. — Craig, Oct 06 '09 at 15:07
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](http://stackoverflow.com/questions/651936/how-can-i-improve-this-mailing-address-sql-server-select-statement/652065#652065) — Mark Brittingham, Oct 06 '09 at 15:03
You could be onto something with UNPIVOT. I read the question and then found this: http://tinyurl.com/y8a7yc9. Will give that a try. — Craig, Oct 06 '09 at 15:11
Good luck. BTW - Cade went the extra mile to get you the detail you needed and his answer looks good on first inspection so, if I were you, I'd give him the "right answer" tag. — Mark Brittingham, Oct 06 '09 at 16:54

score 8 · Accepted Answer · answered Oct 06 '09 at 15:52

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
    )

score 2 · Answer 2 · answered Oct 12 '20 at 04:27

This is MS SQL Server solution using JSON. You don't need to specify the column names and data types. All you need to do is to specify the table name and valid where condition on line 3.

DECLARE @TEMP TABLE (JSONHOLDER NVARCHAR(MAX));
INSERT INTO @TEMP
SELECT (SELECT * FROM YOURTABLE WHERE YOURID = 2589 FOR JSON AUTO) AS JSONHOLDER
SELECT [KEY] as ColumnName, [VALUE] as ColumnValue FROM (SELECT [KEY] AS OLDKEY, [VALUE] AS OLDVALUE  FROM @TEMP A
CROSS APPLY OPENJSON(A.JSONHOLDER)) B CROSS APPLY OPENJSON(B.OLDVALUE)

score 0 · Answer 3 · answered Oct 06 '09 at 14:57

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*/

score 0 · Answer 4 · answered Oct 06 '09 at 14:59

0

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

answered Oct 06 '09 at 14:59

HLGEM

94,695
15
113
186

score 0 · Answer 5 · edited Feb 18 '20 at 05:17

0

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.

edited Feb 18 '20 at 05:17

Nimantha

6,405
6
28
69

answered Oct 06 '09 at 15:07

score -1 · Answer 6 · answered Oct 06 '09 at 14:54

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

answered Oct 06 '09 at 14:54

Arthur Reutenauer

2,622
1
17
15

I do not thing you can PIVOT without knowing what the field names are. But if you can, please post the SQL – Craig Oct 06 '09 at 15:01
Sorry, I don't use SQL Server myself, only read that in the Microsoft docs. – Arthur Reutenauer Oct 06 '09 at 15:06

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

6 Answers6

Linked