Convert columns into rows

• vijayan-159532

  SSC Rookie

  Points: 26

  More actions

  August 4, 2004 at 10:32 am

  #109362

  I am caught in situation which demands converting the columns of a table into rows. I know the vice versa (converting rows into columns) could be done using a CROSSTAB query, but not sure about how to convert columns to rows. A sample of my table is given below

  Code Year Per01  Per02  Per03  Per04

  -------------------------------------------------------------------------

  BUD 2003 3699137.0000 3379137.0000 4051137.0000 2071137.0000

  BUD 2004 3500.0000 19500.0000 36500.0000 50500.0000

  BUD 2005 2500.0000 2500.0000 2500.0000 2500.0000

  I would like to have a SELECT query which would return me a result set like the one shown below

  Code Year Amount

  -----------------------------

  BUD 2003 3699137.0000

  BUD 2003 3379137.0000

  BUD 2003 4051137.0000

  BUD 2003 2071137.0000

  BUD 2004 3500.0000

  BUD 2004 19500.0000

  BUD 2004 36500.0000

  BUD 2004 50500.0000

  BUD 2005 2500.0000

  BUD 2005 2500.0000

  BUD 2005 2500.0000

  BUD 2005 2500.0000

  Note that the columns "Per01", "Per02", "Per03" and "Per04" have been converted into rows in the resultant query.

  Is there a technique with which a SELECT query could be made to return a result set as shown above?

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736494

  More actions

  August 4, 2004 at 11:13 am

  #517686

  something like this will work if you can determine some "key"

  select code, yr, per01

  from mytable

  where yr = 2003

  union

  select code, yr, per02

  from mytable

  where yr = 2003

  union

  select code, yr, per03

  from mytable

  where yr = 2003

  union

  select code, yr, per04

  from mytable

  where yr = 2003

• AKM

  SSCommitted

  Points: 1538

  More actions

  August 5, 2004 at 3:09 am

  #517784

  Steve's solution will (of course) work but I would question whether your specified output set is actually what you want. As stated, you are losing information about which figure corresponds to which period (note that it is not legitimate to use 'row order' for this as formally there is no such thing; SQL Server is free to store rows in any order it chooses, and absent an ORDER BY clause, to return rows in any order it chooses).

  I would suggest that what you probably want by way of output is

  Code Year Period Amount

  -----------------------------

  BUD 2003 1 3699137.0000

  BUD 2003 2 3379137.0000

  BUD 2003 3 4051137.0000

  BUD 2003 4 2071137.0000

  BUD 2004 1 3500.0000

  BUD 2004 2 19500.0000

  BUD 2004 3 36500.0000

  BUD 2004 4 50500.0000

  BUD 2005 1 2500.0000

  BUD 2005 2 2500.0000

  BUD 2005 3 2500.0000

  BUD 2005 4 2500.0000

  In which case the appropriate SQL would be

  select code, year, 1 as Period, Per01 as Amount from mytable
  union
  select code, year, 2 as Period, Per02 as Amount from mytable
  union
  select code, year, 3 as Period, Per03 as Amount from mytable
  union
  select code, year, 4 as Period, Per04 as Amount from mytable
  

  If you want, you could wrap this in

  select code, year, Period, Amount from (

  and

  ) A order by year, Period

  to give the output order you want.