1

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.

  • Apologies, MSSQL – James Hatton Dec 20 '14 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? – Thomas Cleberg Dec 20 '14 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? – Aaron Bertrand Dec 20 '14 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).. – James Hatton Dec 20 '14 at 10:42
2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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