sql convert vertical table to horizontal (deep to wide)

• mtlondon

  SSC Rookie

  Points: 47

  More actions

  October 28, 2010 at 9:50 am

  #230960

  Hi

  I have 2 tables, where one holds Name and address information in a wide format, second table is a deep version where i have the records with field names (i.e. Date Of Birth) in the value and then another field with the actual value.

  I need to merge both tables together to create a new 3rd table and have everything in a wide format, so name and address for one record plus another new column with Date of Birth, so in the end i would just have one row for a person.

  i am using sql 2008, i dont have any examples to show, which makes it difficult to explain.

  a visual example would be

  table 1

  id name address1 address2 postalcode

  100 john 1 anystreet anywhere 125254

  table 2

  id fieldname fieldvalue

  100 dateofbirth 1 jan 1900

  100 age 10

  100 mobile 123456789

  new table should look like

  table 3

  id name address1 address2 postalcode dateofbirth age mobile

  100 john 1 anystreet anywhere 125254 1 jan 1900 10 123456789

  i have a very large dataset so need something generic and fast as there are lots of different field names so i would need to add this to the new table as well.

  I am trying to run the query over millions of records, we have 1TB space, but this query seems to take up all the space and crashes out claiming it could not allocate space.

  can someone please help?

  thanks

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  January 20, 2011 at 10:21 pm

  #1276375

  I know it has been awhile, but were you able to solve your problem?