USE tempdb;

DECLARE @data

TABLE (

state CHAR(6) NOT NULL,

october INT NOT NULL,

november INT NOT NULL,

december INT NOT NULL,

january INT NOT NULL,

february INT NOT NULL

);

INSERT @data

VALUES ('state1', 0721, 1631, 1035, 0821, 0752),

('state2', 1321, 2015, 1997, 0101, 0515),

('state3', 1309, 1360, 0908, 0736, 0665),

('state4', 0272, 1716, 1847, 1804, 0897),

('state5', 0130, 0366, 0394, 0452, 0240),

('state6', 0231, 2992, 2468, 2553, 1693),

('state7', 0612, 1637, 1419, 0941, 0339),

('state8', 1023, 0941, 1772, 1641, 0902);

SELECT P.month AS state,

P.state1,

P.state2,

P.state3,

P.state4,

P.state5,

P.state6,

P.state7,

P.state8

FROM @data D

UNPIVOT (value FOR month IN (october, november, december, january, february)) U

PIVOT (MAX(U.value) FOR U.state IN (state1, state2, state3, state4, state5, state6, state7, state8)) P

ORDER BY

CASE P.month

WHEN 'october' THEN 1

WHEN 'november' THEN 2

WHEN 'december' THEN 3

WHEN 'january' THEN 4

WHEN 'february' THEN 5

ELSE NULL

END;

For a general Transpose function (implemented as a CLR procedure) see http://www.sqlmag.com/articles/index.cfm?articleid=102631. A subscription is required.

Paul