Unpivot Excel Matrix / PivotTable?

Question

Is there a quick way to “expand” an Excel matrix / pivot table (in Excel or elsewhere) without writing macros or other code ?

Again, I can write code (C # or VBA or something else) that does this myself.

I want to know if it is possible to do this quickly without code?

eg. I need to convert this permission matrix (given as Excel spreadsheet / matrix)

into this semi-normalized table (so I can insert it into the SQL database):

eg. in SQL, I could do it like this:

CREATE TABLE dbo.T_DocumentMatrix ( [Function] [varchar](255) NULL, [GROUP-Admin] [varchar](255) NULL, [GROUP-SuperUser] [varchar](255) NULL, [GROUP-Manager] [varchar](255) NULL, [GROUP-OLAP] [varchar](255) NULL, [GROUP-1] [varchar](255) NULL, [GROUP-2] [varchar](255) NULL, [GROUP-3] [varchar](255) NULL, [GROUP-4] [varchar](255) NULL, [GROUP-5] [varchar](255) NULL, [GROUP-6] [varchar](255) NULL, [GROUP-7] [varchar](255) NULL, [GROUP-8] [varchar](255) NULL, [Externals] [varchar](255) NULL ); 

copy-paste data from excel and then

 SELECT * FROM ( SELECT [Function] ,[GROUP-Admin] ,[GROUP-SuperUser] ,[GROUP-Manager] ,[GROUP-OLAP] ,[GROUP-1] ,[GROUP-2] ,[GROUP-3] ,[GROUP-4] ,[GROUP-5] ,[GROUP-6] ,[GROUP-7] ,[GROUP-8] ,[Externals] FROM T_DocumentMatrix ) AS p UNPIVOT ( Rights FOR GroupName IN ( [GROUP-Admin] ,[GROUP-SuperUser] ,[GROUP-Manager] ,[GROUP-OLAP] ,[GROUP-1] ,[GROUP-2] ,[GROUP-3] ,[GROUP-4] ,[GROUP-5] ,[GROUP-6] ,[GROUP-7] ,[GROUP-8] ,[Externals] ) ) AS unpvt ; 

However, this requires changing the table-create script and univot-script for each change in groups ...

Answer 1

Oh, well, it's a little complicated. One of the problems is that master callup shortcuts do not work in non-English versions of excels (hell, I would have an English version at home, but here at work ...)

Here's a good video: https://www.youtube.com/watch?v=pUXJLzqlEPk

But youtube video can be removed, therefore, to make it a reliable SO response:

First you need to go to “Options” and add the “Pivot Table” and “PivotChart Wizard” menu items.

Create a Multiple Consolidation Pivot Table

and use custom option

and select the range, and in the new worksheet

then remove the row and column fields

Double-click the NUMBER (54 in the picture)

and excel will give you half the normalized data.

Answer 2

There is another way through Power Query:

• select your cells
• Data menu> From a table or a range screenshot
• in Power Query Unpivot all columns, save the first and then Transform > Unpivot screenshot
• the table does not unfold. Go to Home > Close and load screenshot
• your unconverted table is here. right click and select Refresh if the source table is updated screenshot