How to invert rows and columns using a T-SQL Pivot Table

Question

I have a query that returns one row. However, I want to invert the rows and columns, meaning show the rows as columns and columns as rows. I think the best way to do this is to use a pivot table, which I am no expert in.

Here is my simple query:

SELECT Period1, Period2, Period3
FROM GL.Actuals
WHERE Year = 2009 AND Account = '001-4000-50031'

Results (with headers):

Period1, Period2, Period3

612.58, 681.36, 676.42

I would like for the results to look like this:

Desired Results:

Period, Amount

Jan, 612.58

Feb, 681.36

Mar, 676.42

This is a simple example, but what I'm really after is a bit more comlex than this. I realize I could produce theses results by using several SELECT commands instead. I'm just hoping someone can shine some light on how to accomplish this with a Pivot Table or if there is yet a better way.

Answer 1

For a fixed set of fields, you can use a UNION of select statements, each statement fetching one of the fields. Standard SQL does not provide a way to transpose the result for a variable number of fields, e.g. select * from table.

If MSSQL has an extension which helps (like pivot), I don't know about it.

Answer 2

Try something like this (tested on MSSQL2008):

DECLARE @Data TABLE(Period1 Decimal(5, 2), Period2 Decimal(5, 2), Period3 Decimal(5, 2))
INSERT @Data
VALUES (612.58, 681.36, 676.42)

SELECT Period, Amount
FROM (SELECT Period1 AS Jan, Period2 AS Feb, Period3 AS Mar FROM @Data) AS D
UNPIVOT (Amount FOR Period IN (Jan, Feb, Mar)) AS U

Update

Based on Jeff's comment, how about this:

DECLARE @Actuals TABLE(Account INT, [Year] INT, Period1 Decimal(5, 2), Period2 Decimal(5, 2), Period3 Decimal(5, 2))
INSERT @Actuals VALUES (1, 2010, 612.58, 681.36, 676.42)
INSERT @Actuals VALUES (1, 2009, 512.58, 581.36, 576.42)

SELECT Account, Period, Amount
FROM
(
    SELECT a.Account, a.Period1 AS Jan, a.Period2 AS Feb, a.Period3 AS Mar, a1.Period1 AS Jan1, a1.Period2 AS Feb1, a1.Period3 AS Mar1
    FROM @Actuals AS a
    LEFT OUTER JOIN @Actuals AS a1 ON a.Account = a1.Account AND a1.[Year] = a.[Year] - 1
    WHERE a.[Year] = 2010
) AS d
UNPIVOT (Amount FOR Period IN (Jan, Feb, Mar, Jan1, Feb1, Mar1)) AS u

or, with an explicit year column:

DECLARE @Actuals TABLE(Account INT, [Year] INT, Period1 Decimal(5, 2), Period2 Decimal(5, 2), Period3 Decimal(5, 2))
INSERT @Actuals VALUES (1, 2010, 612.58, 681.36, 676.42)
INSERT @Actuals VALUES (1, 2009, 512.58, 581.36, 576.42)

SELECT Account, [Year], Period, Amount
FROM
(
    SELECT a.Account, a.[Year], a.Period1 AS Jan, a.Period2 AS Feb, a.Period3 AS Mar
    FROM @Actuals AS a WHERE a.[Year] IN (2009, 2010)
) AS d
UNPIVOT (Amount FOR Period IN (Jan, Feb, Mar)) AS u