I have an issue that has really stumped me in SQL Server.

I have recently had the need to insert a row in to a table, made up from a number of columns. I want to make the one row up from something like the below.

id | CustomerID  | Key         | Value
1       2           Firstname    Tim
2       2           Secondname   Skold
3       2           Age          48
4       2           Gender       Male

So the output should be something like:

customerid  |   Firstname | Secondname | Age | Gender
 2                Tim          Skold       48     Male

I have tried to pivot the result, but can't get it to work, and have also tried to union the results, but this gives the results as a column, not as a single row.

The messy way in which this is being done currently is by joining on to the same table multiple times to get each field (firstname, secondname etc...)

Maybe unpivoting?

Really not sure, thanks for any help...



Updated table view.

  • Apologies, MSSQL Commented Dec 20, 2014 at 0:12
  • 1
    Is id really sequential like that? In this example you want ids 1-4 to show up in the results as id=1? Commented Dec 20, 2014 at 0:34
  • 2
    Is there a second set of rows? Is there another column? How on earth do you know that this Tim belongs to this Skold? Commented Dec 20, 2014 at 2:06
  • Hi, I have updated this, I was at the works christmas party last night, really should refrain from typing after a few glasses of wine...... These values all belong to the same customer (Tim).. Commented Dec 20, 2014 at 10:42

1 Answer 1


You're looking for a pivot. Either of these queries will work: SqlFiddle

/* case */

    , FirstName =max(case when [Key]= 'FirstName' then Value end)
    , LastName  =max(case when [Key]= 'SecondName'  then Value end)
    , Age       =max(case when [Key]= 'Age'       then Value end)
    , Gender    =max(case when [Key]= 'Gender'    then Value end)
from Customers
group by CustomerId 

/* pivot */

select CustomerId, FirstName, SecondName, Age, Gender
  from (Select CustomerId, [Key], Value from Customers) c
    pivot ( max(Value)
      for [Key] in (FirstName, SecondName, Age, Gender)
    ) as p

Schema Setup for SqlFiddle:

    create table Customers (
    id int identity (1,1) not null primary key
  , CustomerID int not null
  , [Key] varchar(32) not null
  , Value varchar(32) 

insert into Customers (CustomerId, [Key], Value) values
  , (2,'SecondName','Skold')
  , (2,'Age','48')
  , (2,'Gender','Male')
  , (3,'FirstName','Sql')
  , (3,'SecondName','Zim')
  , (3,'Age','32')
  , (3,'Gender','Male')



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.