Pivot Query - Assigning Row Value Based on Column Name

Hey all,

i have a question for all the SQL Gurus out there. I have tried to think of a way around, it, but i cannot work it out.

I have a set of data: Samples Below:

Item Warehouse FOR1 FOR2 FOR3 FOR4 FOR5 FOR6 FOR7 FOR8 FOR9 FOR10 FOR11 FOR12 FOR13 FOR14

01-0001 010 329 329 335 343 317 331 328 331 31

I have written a Query to Pivot this data like below:

SELECT WAREHOUSE,ITEM, QTY

FROM

(SELECT ITEM,WAREHOUSE,FOR1,FOR2,for3,for4,for5,for6,for7,for8,for9,for10,
for11,for12,for13,for14,for15,for16,for17,for18,for19,for20,for21,
for22,for23,for24 FROM mvxreport.tbldmsForecasttoMovex) p

UNPIVOT

(QTY FOR tbldmsForecasttoMovex IN (FOR1,FOR2,for3,for4,for5,for6,for7,
for8,for9,for10,for11,for12,for13,for14,for15,for16,for17,for18,for19,
for20,for21,for22,for23,for24))AS unpvt

Warehouse Item Qty
010 01-0001 329
010 01-0001 329
010 01-0001 335
010 01-0001 343
010 01-0001 317
010 01-0001 331
010 01-0001 328
010 01-0001 331
010 01-0001 315
010 01-0001 344
010 01-0001 334
010 01-0001 321
010 01-0001 327
010 01-0001 328
010 01-0001 332
010 01-0001 342
010 01-0001 316
010 01-0001 330
010 01-0001 330
010 01-0001 331
010 01-0001 315
010 01-0001 343
010 01-0001 333
010 01-0001 322

I would like to add some more code to the query, so for each FOR% column,
i can put a numeric value in it. The value will be the numbers ,1 - 24 . One for each line as this represents Months Forward.

Example:

Warehouse Item Qty Month
010 01-0001 329 1
010 01-0001 329 2
010 01-0001 335 3
010 01-0001 343 4
010 01-0001 317 5
010 01-0001 331 6
010 01-0001 328 7
010 01-0001 331 8
010 01-0001 315 9
010 01-0001 344 10
010 01-0001 334 11
010 01-0001 321 12
010 01-0001 327 13
010 01-0001 328 14
010 01-0001 332 15
010 01-0001 342 16
010 01-0001 316 17
010 01-0001 330 18
010 01-0001 330 19
010 01-0001 331 20
010 01-0001 315 21
010 01-0001 343 22
010 01-0001 333 23
010 01-0001 322 24

Does anyone know how i can do this?

Many Thnank

Scotty

Use the below query,

Code Block

SELECT WAREHOUSE,ITEM, QTY, replace(monthfor,'For','') as [Month]

FROM

(SELECT ITEM,WAREHOUSE,FOR1,FOR2,for3,for4,for5,for6,for7,for8,for9,for10,

for11,for12,for13,for14,for15,for16,for17,for18,for19,for20,for21,

for22,for23,for24 FROM mvxreport.tbldmsForecasttoMovex) p

UNPIVOT

(QTY FOR monthfor IN (FOR1,FOR2,for3,for4,for5,for6,for7,

for8,for9,for10,for11,for12,for13,for14,for15,for16,for17,for18,for19,

for20,for21,for22,for23,for24))AS unpvt

|||

I think you can use ROW_NUMBER() function, something like that:

select Warehouse,Item, ROW_NUMBER() OVER (ORDER BY item) as Aqty

from

(

SELECT WAREHOUSE,ITEM, QTY

FROM

(SELECT ITEM,WAREHOUSE,FOR1,FOR2,for3,for4,for5,for6,for7,for8,for9,for10,
for11,for12,for13,for14,for15,for16,for17,for18,for19,for20,for21,
for22,for23,for24 FROM mvxreport.tbldmsForecasttoMovex) p

UNPIVOT

(QTY FOR tbldmsForecasttoMovex IN (FOR1,FOR2,for3,for4,for5,for6,for7,
for8,for9,for10,for11,for12,for13,for14,for15,for16,for17,for18,for19,
for20,for21,for22,for23,for24))AS unpvt

)

|||

thats awesome manivannan, once again you come to the rescue!!

thanks mate

Scotty

|||

recome,

select Warehouse,Item, ROW_NUMBER() OVER (ORDER BY item) as Month

from ...