Hello!

As I've seen others attempting this and it took me awhile to sort it out, I thought I'd share the solution we came up with regarding a Pivot table.

In our case, we used Advanced Custom Fields (ACF) and Custom Post Type to create a customer database capturing all services used by customers. As this data was stored in the postMeta table, we wanted a pivot table showing the customers across the top and services down the left.

As wpDataTables can't do Pivot tables natively, we opted to create a stored procedure and write the data to a new table. The table is updated each time the stored procedure is called (can be done with functions.php each time the page gets called). We will likely call this via some sort of cron script as the data does not change often.

Here is the code to create the stored procedure. Note that post_type = 'customer' is specific to our Custom Post Type. Also worth noting we changed the Y in the THEN ELSE meta value to display a large dot ⬤ but I kept it as a Y if that's what you would want to display.

I also created each of my ACF fields with a naming system that started with customer_ to make it easier to pull the data I wanted.

The resulting data gets written into a new table: customerServices. You can also change this to a different table if you like. This allows me to do a simple select * from customerServices with my wpDataTables query.

BEGIN
  SET SESSION group_concat_max_len = 100000;
    SET @sql1 = NULL;  
    SELECT
    GROUP_CONCAT(DISTINCT CONCAT( 'MAX(DISTINCT CASE WHEN ID = ', ID, ' AND meta_value = 1 THEN ''Y''ELSE '''' END) AS "',  UPPER(REPLACE(post_title,'-',' ')), '"') ORDER BY post_title ASC  )        
    INTO @sql1      
    FROM C2p_postmeta
    LEFT JOIN C2p_posts ON post_id = ID
    WHERE meta_value in ('1') AND post_type = 'customer' 
    ORDER BY CONCAT( 'MAX(DISTINCT CASE WHEN ID = ', ID, ' AND meta_value = 1 THEN ''Y''ELSE '''' END) AS "',  UPPER(REPLACE(post_title,'-',' ')), '"')  ASC;
    SET @sql2 = CONCAT('CREATE TABLE customerServices SELECT REPLACE(REPLACE(UPPER(meta_key),''_'','' ''), ''CUSTOMERS'','''') as SERVICES, ', @sql1,
                       '
                       FROM C2p_postmeta
                       LEFT JOIN C2p_posts ON post_id = ID
                       WHERE meta_value in (''0'',''1'') AND post_type = ''customer'' AND meta_key LIKE ''customers_%''
                       GROUP BY meta_key
                       ORDER BY services
                       ' );
DROP TABLE IF EXISTS customerServices;
    PREPARE stmt FROM @sql2;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END

I also rotated the text of my customer columns to make nice clean columns. because the size of my table is large, I also froze the first column. Here's the CSS. I'm still doing some clean-up so I'm suspect some of the CSS needs a little attention yet.

/** Rotate header text **/
table.wpDataTable.wpDataTableID-7 th.wdtheader {
    
writing-mode: tb-rl;
        transform: rotate(-180deg);
    
}
/** Rotate header text **/
table.wpDataTable.wpDataTableID-7 th.wdtheader.sort.expand.column-services.sorting_asc {
    
writing-mode: tb-lr;
        transform: rotate(-90deg);
text-align: center;
vertical-align: bottom;
width: 100%;
}
table.wpDataTable th.wdtheader.sort.expand.column-services.sorting_asc {
   position: sticky;
   position: -webkit-sticky;
   left: 0;
   z-index: 1;
   background: grey;
   font-weight:bold;
}
table.wpDataTable td.column-services.sorting_1 {
   position: sticky;
   position: -webkit-sticky;
   left: 0;
   z-index: 1;
   background: grey;
   font-weight:bold;
}

Hopefully this helps! Also, this was not possible without a good friend and great developer to assist.

Screenshot sample:

Be well!

-Scott