Transpose SQL Table

Question

I would like to Transpose test Table to Flip Table Based on Prop_id. SQL Fiddle by filling the Null value in Flip Table from test.cost.

Many Thanks

Edit

Table structures taken from the SqlFiddle:

Source:

CREATE TABLE test(
    Prop_id varchar(10),
    year varchar(4),
    cost float(8)
)

Destination:

CREATE TABLE flip(
   Prop_id varchar(10),
   y1997 varchar(4),
   y1998 varchar(4),
   y1999 varchar(4),
   y2000 varchar(4),
   y2001 varchar(4),
   y2002 varchar(4),
   y2003 varchar(4),
   y2004 varchar(4),
   y2005 varchar(4),
   y2006 varchar(4),
   y2007 varchar(4),
   y2008 varchar(4),
   y2009 varchar(4),
   y2010 varchar(4),
   y2011 varchar(4),
   y2012 varchar(4),
   y2013 varchar(4)    
)

I need to fill the Flip Table from test table by filling the years from the cost field. — user2722043, Mar 26 '14 at 08:57
do you plan to have a table that you expand with a new year column every year ? — t-clausen.dk, Mar 26 '14 at 09:05

score 1 · Accepted Answer · edited May 23 '17 at 12:05

This is a straightforward PIVOT:.

INSERT INTO Flip(Prop_id, y1997, y1998, y1999, y2000, y2001, ... )
  SELECT prop_id, [1997], [1998], [1999], [2000], [2001], ...
  FROM
    test
  PIVOT
  (
   SUM(cost)
   for year IN ([1997], [1998], [1999], [2000], [2001], ...)
  )pvt;

SqlFiddle here

If you don't want to manually specify the column names, you can change this into a dynamic query like so.

One possible suggestion is to change the columns of flip y1997 etc to match the same type as the source table (test.cost)

And if possible, use DECIMAL to model currency - floats and doubles are a bad fit to money due to rounding issues

StuartLC, Many thanks that work Perfect As I want, I do appreciate your suggestion. — user2722043, Mar 26 '14 at 09:19

Transpose SQL Table

1 Answers1