StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Try something like this (tested on MSSQL2008):</p> <pre><code>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 </code></pre> <p><strong>Update</strong></p> <p>Based on Jeff's comment, how about this:</p> <pre><code>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 </code></pre> <p>or, with an explicit year column:</p> <pre><code>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 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload