1

I'm creating a SQL table from an Excel file. The excel file is not in a transactional format though and I'm having a hard time figuring the SQL to do this correctly.

Excel file has the following column names:

Dept code, Dept Name, ..., 1962, 1963, 1964, ..., 2022

Each field in the "year" columns have a dollar value in them.

I'd like to import this in a transactional format with a record for each dept code/name and a 'year' column, and a 'value' column.

Output would look like this:

Columns: 

Dept Code, Dept Name, Year, Value

Data: 

001, Marketing, 1962, $19,000
001, Marketing, 1963, $20,000         
etc, etc...

Any hints on how to do this, elegantly?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ben McDonald
  • 13
  • 3

1 Answers1

0

String the non-year/value entries together with something like:

=A2&"|"&B2&"|"&C2

Unpivot and split the combined column with Text to Columns and | as the delimiter.

pnuts
  • 58,317
  • 11
  • 87
  • 139