Having trouble pivot (transposing), HELP!!

Question

Having trouble pivot (transposing), HELP!!

Eli Vergara 1

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:

But I want to convert it to something like this:

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!

CathyJi-MSFT 22,396 Reputation points Microsoft External Staff

2021-10-11T02:04:26.797+00:00

Hi @Eli Vergara ,

We can using SQL Server Import and Export Wizard to import data from Excel to SQL server tables. But we could not change the table structure during the process. So suggest you add the MS Excel tag office-excel-itpro , people there will help you to convert the tables in Excel to a table that you want. Then we could help you to import data from Excel to SQL server table.

For how to add tag to MS Q&A. Please follow below steps.
Ronen Ariely 15,206 Reputation points

2021-10-11T02:52:48.933+00:00

Good day,

But we could not change the table structure during the process.

There are multiple ways to do this task - in fact, this is part of the meaning of ETL tasks which we do on Data. Most ETL tools can do it.

For example SSIS supports pivot operations on input data and it is probably the most common Microsoft ETL tool for on-premises. If we want a cloud solution from Microsoft then you can use Azure Data Factory which also provide pivot operation during migrating of the data. I am pretty sure that most ETL tools can do this basic task (pivot is very common needs)

With that said, you can also do it and more directly by using OPENROWSET and using SQL Server build-in pivot feature - this is my preferred option usually for one-time-tasks as such.

Someone that have experience with Excel can actually do it in the Excel using Power queries before the migration of the data. In this case, this might be the best option.
CathyJi-MSFT 22,396 Reputation points Microsoft External Staff

2021-10-11T03:24:46.367+00:00

Have a good day!

Thanks for pointing out. Maybe my description is not accurate enough. I discussed this issue with my SSIS colleague. It is hard to achieve this from SSIS in this case. OP merge the cells in Excel, if we using SSIS to transform the data, the result is like below screenshot. It will automatically reset the column name. I am not familiar with SSIS, If my description is inaccurate, please feel free to point out.

In this case, I still suggest OP to convert the tables in Excel then import the data to SQL server.
Ronen Ariely 15,206 Reputation points

2021-10-11T04:49:26.827+00:00

It is hard to achieve this from SSIS in this case.

True :-( but can be done probably (skip the first line as a start might solve the main issue) - not my choice.

In this case, I still suggest OP to convert the tables in Excel then import the data to SQL server.

I totally agree. This will be simpler to prepper manually in the excel

This is very complex task for all ETL tools, since the content of the Excel sheet is not well structured as tabular structure. Might need some manual work, but this is OK in one-time-task I think.

I explained it in my answer the main steps :-)

The first task should be re-structure the data in a tabular structure - add the dates as a new column.

The best tool for the first task is the Excel itself. Since this is a one-time-task, I would probably do it manually ort with some excel functions or even power query.

But since this question is in SQL Server section (tag), I would use the steps as explained in my answer

Yes, it is complex, and if the system was well designed, then this requirement would never have arisen
Ronen Ariely 15,206 Reputation points

2021-10-11T04:50:33.45+00:00

Could be much simpler if we have the file for testing and understanding the structure better :-)

1 answer

Your answer

CathyJi-MSFT 22,396 Reputation points Microsoft External Staff

2021-10-11T02:04:26.797+00:00

Hi @Eli Vergara ,

We can using SQL Server Import and Export Wizard to import data from Excel to SQL server tables. But we could not change the table structure during the process. So suggest you add the MS Excel tag office-excel-itpro , people there will help you to convert the tables in Excel to a table that you want. Then we could help you to import data from Excel to SQL server table.

For how to add tag to MS Q&A. Please follow below steps.
Ronen Ariely 15,206 Reputation points

2021-10-11T02:52:48.933+00:00

Good day,

But we could not change the table structure during the process.

There are multiple ways to do this task - in fact, this is part of the meaning of ETL tasks which we do on Data. Most ETL tools can do it.

For example SSIS supports pivot operations on input data and it is probably the most common Microsoft ETL tool for on-premises. If we want a cloud solution from Microsoft then you can use Azure Data Factory which also provide pivot operation during migrating of the data. I am pretty sure that most ETL tools can do this basic task (pivot is very common needs)

With that said, you can also do it and more directly by using OPENROWSET and using SQL Server build-in pivot feature - this is my preferred option usually for one-time-tasks as such.

Someone that have experience with Excel can actually do it in the Excel using Power queries before the migration of the data. In this case, this might be the best option.
CathyJi-MSFT 22,396 Reputation points Microsoft External Staff

2021-10-11T03:24:46.367+00:00

Have a good day!

Thanks for pointing out. Maybe my description is not accurate enough. I discussed this issue with my SSIS colleague. It is hard to achieve this from SSIS in this case. OP merge the cells in Excel, if we using SSIS to transform the data, the result is like below screenshot. It will automatically reset the column name. I am not familiar with SSIS, If my description is inaccurate, please feel free to point out.

In this case, I still suggest OP to convert the tables in Excel then import the data to SQL server.
Ronen Ariely 15,206 Reputation points

2021-10-11T04:49:26.827+00:00

It is hard to achieve this from SSIS in this case.

True :-( but can be done probably (skip the first line as a start might solve the main issue) - not my choice.

In this case, I still suggest OP to convert the tables in Excel then import the data to SQL server.

I totally agree. This will be simpler to prepper manually in the excel

This is very complex task for all ETL tools, since the content of the Excel sheet is not well structured as tabular structure. Might need some manual work, but this is OK in one-time-task I think.

I explained it in my answer the main steps :-)

The first task should be re-structure the data in a tabular structure - add the dates as a new column.

The best tool for the first task is the Excel itself. Since this is a one-time-task, I would probably do it manually ort with some excel functions or even power query.

But since this question is in SQL Server section (tag), I would use the steps as explained in my answer

Yes, it is complex, and if the system was well designed, then this requirement would never have arisen
Ronen Ariely 15,206 Reputation points

2021-10-11T04:50:33.45+00:00

Could be much simpler if we have the file for testing and understanding the structure better :-)

Answer 1

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 :

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

Ronen Ariely 15,206 Reputation points

2021-10-12T03:52:27.59+00:00

Any update @Eli Vergara ?
Eli Vergara 1 Reputation point

2021-10-12T13:14:21.047+00:00

Thank you pituach, let me work on this and see if it works. Like i said, I'm very ignorant of some of these concepts, but I'm willing to try and learn., But I'll probably need a few days to study these suggestions. I appreciate all your responses!!
Ronen Ariely 15,206 Reputation points

2021-10-12T17:16:36.22+00:00

Hi,

Thank you pituach, let me work on this and see if it works.

Sound great and I mostly love the sentence "I'm willing to try and learn" as this is why we are here

I appreciate all your responses!!

You are most welcome

Note: Remember, that we can help you work on it if you provide your Excel file, after you try it yourself and learn the topic a bit more

Share via

Having trouble pivot (transposing), HELP!!

1 answer

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

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

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

Your answer