CODEWITHSUNDEEP
sql-servert-sql
Attie Wagner
Attie Wagner

Reputation: 1362

Pivot / Unpivot the following data

I'm new to Pivots so I'm not exactly sure how I would need to do this.

I have the following data:

Budgets

I need to pivot/unpivot the above data.

I need the following Columns:

  1. GLLink
  2. AccType
  3. Budget
  4. Budget Value

I've tried developing the code, but I'm afraid I am doing it completely wrong:

select
*
from
(
select
*
from    Budgets
)   x
unpivot
(
GLLink for Budget in ([AccType])
)   p
order by GLLink, Budget

The results I would require should look like this:

enter image description here

Upvotes: 2

Views: 51

Answers (1)

George Menoutis
George Menoutis

Reputation: 7250

Assuming the data you showed us are in a table named 'yourtable', here is the cross apply way:

select t.GLLink,t.Acctype,u.*
from yourtable t
cross apply
(
    values 
    ('Budget01',Budget01),
    ('Budget02',Budget02),
    ('Budget03',Budget03),
    ('Budget04',Budget04),
    ('Budget05',Budget05),
    ('Budget06',Budget06),
    ('Budget07',Budget07)
)u(Budget,[Budget Value])

The way it works: cross apply "sees" one row each time. For this row, it uses the values() operator to create a table, so we make the pairs as you need them. In the end, we give a name to this table (u for unpivoted), and also name the columns.

Converting columns to rows is unpivoting (what you need), pivoting is the reverse.

Upvotes: 4

Related Questions