Help with a data transform

[hobanwashburne]

I have a data set in the format:

Reference Number, Date01, Date02,...., Date12, Qty01, Qty02,..., Qty12

How can I transpose/crosstab this data resulting in:

Reference Number, Date, Qty

Please see attached...

[jpapador · Accepted solution]

PFA, I assumed that Qty01 was related to Month01 and so on

[MarcoWedel]

Hi,

another solution without hardcoding the field names could be:

tabInput:
CrossTable(ColNam, ColVal)
LOAD * INLINE [
    ReferenceNumber, Month01, Month02, Month03, Month04, Month05, Month06, Month07, Month08, Month09, Month10, Month11, Month12, Qty01, Qty02, Qty03, Qty04, Qty05, Qty06, Qty07, Qty08, Qty09, Qty10, Qty11, Qty12
    Abc01, 1/1/2014, 2/1/2014, 3/1/2014, 4/1/2014, 5/1/2014, 6/1/2014, 7/1/2014, 8/1/2014, 9/1/2014, 10/1/2014, 11/1/2014, 12/1/2014, 10, 0, 10, 0, 10, 0, 10, 0, 10, 0, 10, 0
    Abc02, 3/1/2014, 4/1/2014, 5/1/2014, 6/1/2014, 7/1/2014, 8/1/2014, 9/1/2014, 10/1/2014, 11/1/2014, 12/1/2014, 1/1/2015, 2/1/2015, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100
    Abc03, 6/1/2014, 7/1/2014, 8/1/2014, 9/1/2014, 10/1/2014, 11/1/2014, 12/1/2014, 1/1/2015, 2/1/2015, 3/1/2015, 4/1/2015, 5/1/2015, 500, 800, 1000, 1000, 1000, 1000, 1000, 1000, 1000, 1000, 1000, 1000
    Abc04, 1/1/2014, 2/1/2014, 3/1/2014, 4/1/2014, 5/1/2014, 6/1/2014, 7/1/2014, 8/1/2014, 9/1/2014, 10/1/2014, 11/1/2014, 12/1/2014, 55, 55, 55, 55, 55, 55, 55, 55, 55, 55, 55, 55
    Abc05, 4/1/2014, 5/1/2014, 6/1/2014, 7/1/2014, 8/1/2014, 9/1/2014, 10/1/2014, 11/1/2014, 12/1/2014, 1/1/2015, 2/1/2015, 3/1/2015, 55, 55, 55, 55, 55, 55, 55, 55, 55, 55, 55, 55
];

tabOutput:
Generic LOAD
  ReferenceNumber,
  Right(ColNam,2) as ID,
  Left(ColNam,Len(ColNam)-2),
  ColVal
Resident tabInput;

DROP Table tabInput;

hope this helps

regards

Marco