CODEWITHSUNDEEP
sqlsql-serverjoinaddition
dare2k
dare2k

Reputation: 437

SQL Add next year data to columns

I want to make a forecast based on weeks. I have the table like this.

personName, year, week1, ..., week52.

And would like to add the next year to columns based on how much week i need. Some kind of select in select.

The final select should look like this:

select personName, week1,week2,..., (nextyear)week1,(nextyear)week2,.. from x

Any ideas how can I move the next year up to columns?

Upvotes: 0

Views: 185

Answers (1)

Fluffeh
Fluffeh

Reputation: 33512

You want to make rows for your data instead of columns - for the exact reason that you are asking about.

Something like this structure:

personName Year Week

Then you can do a query like:

select week from table1 where year=$year-1 and week=$week

or pure SQL like this:

select
    a.personName
    a.week,
    a.year
from
    table1 a
        join table1 b
            on a.personName=b.personName
            and a.week=b.week
            and a.year=b.year-1

However, this similar approach could be used with your table something like this:

select
    a.personName
    a.week1,
    a.week2,
    a.week3,
    b.week1,
    b.week2,
    b.week3,
    a.year
from
    table1 a
        join table1 b
            on a.personName=b.personName
            and a.year=b.year-1

Upvotes: 1

Related Questions