CODEWITHSUNDEEP
sqlsql-serverunpivot
leodamatta
leodamatta

Reputation: 15

Different table layouts

I have a table that looks like this:

|| id  | year |  userCode  |  jan  |  feb  |  ... ... ... |  dec  ||

The columns jan - dec holds value (money) data in it. I want it to look like this:

||id  |  year  |  month  |  userCode  |  value ||

Here's the thing: I can have two values for the same userCode in the same month (and I need them both), so I can't just use SUM. Any ideas?

Thanks in advance!

Upvotes: 0

Views: 252

Answers (3)

Taryn
Taryn

Reputation: 247820

In SQL Server 2005+ you can easily do this using the UNPIVOT function which transforms data from columns into rows.

The code would be similar to this:

select id, year, month, usercode, value
from yourtable
unpivot
(
  value
  for month in (Jan, Feb, Mar, Apr, May,
                Jun, Jul, Aug, Sep, Oct,
                Nov, Dec)
) unpiv

Once the data in the rows, then you can perform any type of aggregation needed.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270463

You can do this with union all:

select year, month, userCode, jan, 'jan' as which as value from t union all
select year, month, userCode, feb, 'feb' as which as value from t union all
. . .
select year, month, userCode, dec, 'dec' as which as value from t

I added an extra column which so you would know where the data came from.

Upvotes: 0

Naveed Butt
Naveed Butt

Reputation: 2901

You can do it with two tables...

One table with Years and Months in it

YearMonths

|| Id | Year | Month | UserCode ||

YearMonthsValues

|| Id | YearMonthId | Value ||

Upvotes: 0

Related Questions