CODEWITHSUNDEEP
sql-serverdatabase
Daniel Rodriguez
Daniel Rodriguez

Reputation: 33

How to rename column before unpivot? SQL Server

Help I want to rename a column before using the unpivot method

My table is like this:

List item

id| value| ENE| FEB| MAR| ABR| MAY  
1 | dsads|2000|2334|2344|2344|2344

after unpivot I get something like this

id| value| month| amount
1 |  dads| ENE  | 2000
2 |  sadf| FEB  | 2334

but I want something like this 

id| value| month| amount
1 |  dads| 01   | 2000
2 |  sadf| 02   | 2334

This is my query

select 
    [año], [Empresa], [Region], [Suc], [CC], [Cuenta], 
    [Subcuenta], [Descripcion], Periodo, Importe
from 
    (select * from [dbo].['P Cargado$']) S
unpivot 
    (Importe for Periodo in
        ([ENE], [FEB], [MAR], [ABR], [MAY], [JUN], [JUL], [AGO], [SEP],[OCT], [NOV], [DIC])
    ) AS unpvt;

Upvotes: 3

Views: 645

Answers (1)

SqlZim
SqlZim

Reputation: 38043

If you use cross apply(values ..) to unpivot, you could do so like this:

select t.id, t.value, x.*
from t
cross apply (values (1,ene),(2,feb),(3,mar),(4,abr),(5,may)) x (Mnth,Amount)

rextester demo: http://rextester.com/SZDP50356

returns:

+----+-------+------+--------+
| id | value | Mnth | Amount |
+----+-------+------+--------+
|  1 | dsads |    1 |   2000 |
|  1 | dsads |    2 |   2334 |
|  1 | dsads |    3 |   2344 |
|  1 | dsads |    4 |   2344 |
|  1 | dsads |    5 |   2344 |
+----+-------+------+--------+

Upvotes: 3

Related Questions