Help with Excel (Un)pivot Import

• Tom257

  SSChampion

  Points: 11543

  More actions

  September 17, 2013 at 4:24 am

  #395498

  This is probably really simple but I just can't see it this morning.

  I have an Excel table like this

  | AGE | 2.00% | 2.25% | 2.50%| 2.75% | 3.00% |

  | 23 | 27 | 29 | 31 | 33 | 35 |

  | 26 | 28 | 29 | 31 | 33 | 35 |

  | 28 | 28 | 30 | 32 | 33 | 35 |

  And I want to flatten this data out into SQL Server with a table as follows

  | AGE | Rate | Amount |

  | 23 | 2.00 | 27 |

  | 23 | 2.25 | 29 |

  | 23 | 2.50 | 31 |

  | 23 | 2.75 | 33 |

  | 23 | 3.00 | 35 |

  | 26 | 2.00 | 28 |

  | 26 | 2.25 | 29 |

  | 26 | 2.50 | 31 |

  | 26 | 2.75 | 33 |

  | 26 | 3.00 | 35 |

  | 28 | 2.00 | 28 |

  | 28 | 2.25 | 30 |

  | 28 | 2.50 | 32 |

  | 28 | 2.75 | 33 |

  | 28 | 3.00 | 35 |

  Table definition for import table

  CREATE TABLE GADRates (

  Age INT NOT NULL,

  Rate DECIMAL(6,3) NOT NULL,

  Amount MONEY NOT NULL );

  Example Excel table attached.

  This imports as follows:

  CREATE TABLE [dbo].[Sheet1$](

  [AGE] [float] NULL,

  [2#00%] [money] NULL,

  [2#25%] [money] NULL,

  [2#50%] [money] NULL,

  [2#75%] [money] NULL,

  [3#00%] [money] NULL,

  [3#25%] [money] NULL,

  [3#50%] [money] NULL,

  [3#75%] [money] NULL

  )

  To shortcut excel import here is a data creation script

  INSERT [dbo].[Sheet1$] ([AGE], [2#00%], [2#25%], [2#50%], [2#75%], [3#00%], [3#25%], [3#50%], [3#75%])

  SELECT 23, 27.0000, 29.0000, 31.0000, 33.0000, 35.0000, 36.0000, 38.0000, 40.0000 UNION ALL

  SELECT 26, 28.0000, 29.0000, 31.0000, 33.0000, 35.0000, 37.0000, 39.0000, 40.0000 UNION ALL

  SELECT 27, 28.0000, 30.0000, 31.0000, 33.0000, 35.0000, 37.0000, 39.0000, 41.0000 UNION ALL

  SELECT 28, 28.0000, 30.0000, 32.0000, 33.0000, 35.0000, 37.0000, 39.0000, 41.0000 UNION ALL

  SELECT 29, 28.0000, 30.0000, 32.0000, 34.0000, 35.0000, 37.0000, 39.0000, 41.0000 UNION ALL

  SELECT 30, 29.0000, 30.0000, 32.0000, 34.0000, 36.0000, 38.0000, 39.0000, 41.0000 UNION ALL

  SELECT 31, 29.0000, 31.0000, 32.0000, 34.0000, 36.0000, 38.0000, 40.0000, 42.0000 UNION ALL

  SELECT 32, 29.0000, 31.0000, 33.0000, 34.0000, 36.0000, 38.0000, 40.0000, 42.0000 UNION ALL

  SELECT 33, 30.0000, 31.0000, 33.0000, 35.0000, 36.0000, 38.0000, 40.0000, 42.0000 UNION ALL

  SELECT 34, 30.0000, 32.0000, 33.0000, 35.0000, 37.0000, 39.0000, 40.0000, 42.0000 UNION ALL

  SELECT 35, 30.0000, 32.0000, 34.0000, 35.0000, 37.0000, 39.0000, 41.0000, 43.0000 UNION ALL

  SELECT 36, 31.0000, 32.0000, 34.0000, 36.0000, 37.0000, 39.0000, 41.0000, 43.0000 UNION ALL

  SELECT 37, 31.0000, 33.0000, 34.0000, 36.0000, 38.0000, 40.0000, 41.0000, 43.0000;

  When you import the Excel into SQL Server, it changes the 2.00% into column names [2#00%] - and my best effort so far doesn't even work or take into account the amount column.

  nonworking code

  SELECT [Age]

  ,[2#00%]

  ,[2#25%]

  ,[2#50%]

  ,[2#75%]

  ,[3#00%]

  FROM

  ( SELECT [Age], [2#00%], [2#25%], [2#50%], [2#75%], [3#00%]

  FROM [dbo].[Sheet1$] ) AS PVT

  UNPIVOT

  (

  [AGE]

  FOR RATE IN ([2#00%], [2#25%], [2#50%], [2#75%], [3#00%])

  ) AS UNPVT