Search code examples
sqlsql-serverunpivot

How do i unpivot or flatten this table

I have the following table in my sqlserver database: 

   FiguresYear FiguresMonth Apple Orange Banana Grape
    2012        Jan          10    12     15     20
    2013        Jan          1     2      3      5

I want to run a query which returns the following format:

FiguresYear FiguresMonth FruitName FruitValue
2012        Jan          Apple     10
2012        Jan          Orange    12
2012        Jan          Banana    15
2012        Jan          Grape     20
2013        Jan          Apple     1
2013        Jan          Orange    2
2013        Jan          Banana    3
2013        Jan          Grape     5

I am trying to use the unpivot function but cant quite get it working. Does anyone know how to do this with or without unpivot?

Solution

  • Like this:

    SELECT *
FROM tablename AS t
unpivot
(
  FruitValue
  FOR FruitName IN([Apple], [Orange], [Banana], [Grape])
) AS u;