Turning multiple fields in column to row

Question

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

James.

EDIT***

Updated table view.

Answer 1

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

/* case */

select
      CustomerId
    , 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,'FirstName','Tim')
  , (2,'SecondName','Skold')
  , (2,'Age','48')
  , (2,'Gender','Male')
  , (3,'FirstName','Sql')
  , (3,'SecondName','Zim')
  , (3,'Age','32')
  , (3,'Gender','Male')

Links

• Technet - Using PIVOT and UNPIVOT
• Cross Tabs and Pivots, Part 1 - Jeff Moden