given: I have a source table that contains different attributes for footwear in multiple boolean fields that indicate whether the footwear for that attribute is available. 1=Available, 0=Not available. Sample data is as follows -

There are also some attribute columns, such as o_casual, o_formal, o_ethnic, m_canvas, m_leather, m_silicon, p_plain, p_textured, p_funky, etc., and all attribute columns have binary values ​​of their respective prod_id. There are over 50k prod_ids.

ask: I need a rotated distribution of the availability of each pair of attributes, and the cell value should indicate the count of prod_id for a given pair at the intersection. For example, a prod_id with attributes "s_8" and "c_black" has a count of 4 (where s_8=1 and c_black=1). It should appear in the view below, which can be useful for further analysis.

Intuitively, the output table is mirrored across the diagonal, and the diagonal cells will contain the total number of prod_ids for that particular attribute.

I am new to SQL. Please help me find a logic through which I can achieve the desired pivot view. Note that the data is large, so consider scalable solutions. If the question is unclear and you need some explanation to clarify, please let me know.