UNPIVOT crosstab sheet using SQL and get count of items.

[Junoon]

hi,

i am trying to pull in a table from an external workbook into a ADO recordset and unpivot all the columns (approx 28 columns) starting with "Global" in their names and in the next column list their items and also their counts.

i have attached a sample workbook which shows how i am currently doing it manually to create the table in the Output sheet from the Data sheet table which is actually the table from the input file.

the counts are to be based on the ItemCreationDate column and any item which was created >=2015 shall be counted in a column called "New". Similarly, any item which was created <2015, shall be counted in a column called "Old". then i am taking a % of each item found in that Global column from that Global column total. the Index column is just a New/Old calculation.

i dont know how to form the appropriate SQL query to do the unpivoting of each Global column and counting of their items. i hope the sample workbook shall make it clear.