Unpivotting multiple columns - substring of column name as a new column with CROSS APPLY

Question

I have a table with the following format

YEAR, MONTH, ITEM,  REQ_QTY1, REQ_QTY2 , ....REQ_QTY31 ,CONVERTED1, CONVERTED2 ....CONVERTED31

Where the suffix of each column is the day of the month.

I need to convert it to the following format, where Day_of_month is the numeric suffix of each column

YEAR, MONTH, DAY_OF_MONTH, ITEM, REQ_QTY, CONVERTED

I thought of using CROSS APPLY to retrieve the data, but I can't use CROSS APPLY to get the "Day of Month"

SELECT A.YEAR, A.MONTH, A.ITEM, B.REQ_QTY, B.CONVERTED
FROM TEST A
CROSS APPLY
(VALUES
   (REQ_QTY1, CONVERTED1),
 (REQ_QTY2, CONVERTED2),
 (REQ_QTY3, CONVERTED3),
......
 (REQ_QTY31, CONVERTED31)
 )B (REQ_QTY, CONVERTED)

The only way I found is to use a nested select with inner join

SELECT A.YEAR, A.MONTH,  A.DAY_OF_MONTH,  A.ITEM,A.REQ_QTY, D.CONVERTED FROM
  (SELECT YEAR, MONTH, ITEM, SUBSTRING(DAY_OF_MONTH,8,2) AS DAY_OF_MONTH, REQ_QTY FROM TEST 
  UNPIVOT
     (REQ_QTY FOR DAY_OF_MONTH IN ([REQ_QTY1],[REQ_QTY2],[REQ_QTY3],......[REQ_QTY30],[REQ_QTY31])
     ) B
  ) A
  INNER JOIN (SELECT YEAR, MONTH, ITEM, SUBSTRING(DAY_OF_MONTH,10,2) AS DAY_OF_MONTH, CONVERTED FROM TEST 
  UNPIVOT
     (CONVERTED FOR DAY_OF_MONTH IN ([CONVERTED1],[CONVERTED2],[CONVERTED3],....[CONVERTED30],[CONVERTED31])
     ) C
  ) D
  ON D.YEAR = A.YEAR AND D.MONTH = A.MONTH AND D.ITEM = A.ITEM AND D.DAY_OF_MONTH = A.DAY_OF_MONTH

Is there a way to use CROSS APPLY and yet get the DAY_OF_MONTH out?

Answer 1

Well, I found a way using CROSS APPLY, but instead of taking a substring, I'm basically hardcoding the days. Works well enough so...

SELECT A.YEAR, A.MONTH, A.ITEM, B.DAY_OF_MONTH, B.REQ_QTY, B.CONVERTED
FROM TEST A
CROSS APPLY
(
  VALUES
  ('01', REQ_QTY1, CONVERTED1),
  ('02', REQ_QTY2, CONVERTED2),
  ('03', REQ_QTY3, CONVERTED3),
  ('04', REQ_QTY4, CONVERTED4),
   ......
  ('31', REQ_QTY31, CONVERTED31)
  ) B (DAY_OF_MONTH, REQ_QTY, CONVERTED)