Having trouble pivot (transposing), HELP!!

Eli Vergara 1 Reputation point
2021-10-09T14:09:33.757+00:00

Hi, noob here, first time asking.

I have an Excel spreadsheet with thousands of scores. I want to transpose this into more manageable data to use in SQL.

My spreadsheet, which has 100 different measure-scores (rows, not shown) is formatted like this:

139009-image.png

But I want to convert it to something like this:
139137-image.png

I have 2+ years of data, so clearly I need to find a way... I would love to run a PIVOT SQL query to the spreadsheet but I am too new to SQL and cannot think of how.
I have tried excel copy/paste transposed, but I wasn't able to do it in an efficient way.

Any help would be greatly appreciated!! ☺

Thanks!

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,351 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Ronen Ariely 15,106 Reputation points
    2021-10-11T03:54:41.193+00:00

    Good day Eli Vergara < @Eli Vergara >

    As I mentioned in the comment, there are multiple ETL tools which you can use for the task. If this question was asked in SSIS then I would probably provide a solution using SSIS and if it was asked in Excel then probably I would present solution with power query and so on, but here you choose to discuss SQL Server :-)

    Therefore, Let's assume that we want to do it in the SQL Server side

    There are two main approaches for migrating the data from Excel to SQL Server:

    (1) Export to text file first and import from the text file

    (2) Directly import from Excel to SQL Server

    In each approach you have several tools which you can use. Some of these allow to do some ETL work during the migration and some not. The following document present the tools for each approach and how to do it:

    https://learn.microsoft.com/en-us/sql/relational-databases/import-export/import-data-from-excel-to-sql

    ----------

    The main complexity in your scenario is that we do not know what is the exact format of the source file. We have no idea about the amount of months for example or the amount of users in the row. This make the task complex and almost impossible for us since we cannot read mind and we have no access to your machine/files.

    Let's use a staging table - a simple table with no indexes where we will import the data first

    Issue!!! Your Excel sheet does not have a tabular structure! SQL Server is a tabular database so first you need to organize the data in a table like structure. The months in the Excel are presented above the values which presented in tabular structure. You need to add the month to the table part as a new column.

    Step one: Import each month separately and add a column with the name of the month

    The result should be simple table that looks like :

    139240-image.png

    Can you so this step?

    Note! you can also do it in the Excel

    Please try to finish this step, and if you have any issue then we will need more information and maybe a sample of the excel file, in order to fully understand the structure of the source file. In this case, please provide sample of Excel file and your expected result in this specific sample data.

    Step two: now you can use PIVOT to get the requested structure as in the question

    Try to work in these steps and inform us if something is not clear or you don't succeed to apply - this mean that we will probably ask for more information