Search code examples
excelmatrixpivotunpivot

Unpivot an Excel matrix/pivot-table?

Is there a quick way to "unpivot" an Excel matrix/pivot-table (in Excel or elsewhere), without writing macros or other code ?
Again, I can write code (C# or VBA or whatever) that does that myselfs.
I want to know if it is possible to do it without code, quickly ?

E.g. I need to convert this permission matrix (given as Excel-table/matrix)

Pivoted

into this half-normalized table (so I can insert it into a SQL database):

Unpivoted

e.g. 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 the 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, that requires I change the table-create script and the unpivot-script for every change in groups...

Solution

  • Oh, well, it's a little complicated. One of the problems is, the wizard-callup shortcuts don't work in non-english versions of excels (damn, at home I would have the English version, but here at work...)

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

    But youtube videos can be deleted, so to make it a solid SO answer:

    First, you need to go to "Options", and add the menuband-item "Pivot table and PivotChart Wizard".

    Options

    Wizard

    Create a multiple consolidation pivot table Multiple-consolid

    and use the custom variant
    custom variant

    and select the range, and in new work sheet range

    then delete rows and columns fields

    delete rows and columns

    Double click on the NUMBER (54 in the picture)

    values consolidtions

    and excel will give you the halfway normalized data.

    halfway