Group by multiple columns with different members of the same hierarchy?

Question

I have customer data in a table structure like this:

customerID | productLine1 | productLine2 | productLine3
1          | 1            | null         | null
2          | 2            | 1            | null
3          | 4            | 2            | 1

Each customer is assigned to at least 1 product line but a maximum of three product lines.

I would now like to see how many customers I have for each product line. Any ideas on how to solve this?

Important: I do not wish to turn columns into rows (which would be a solution). So the result would be:

productLine "1" --> 3 customers
productLine "2" --> 2 customers
productLine "4" --> 1 customer

Answer 1

Try this ( replace tbl with your table name):

WITH Unpivoted
AS (
    SELECT customerID
        ,ProductLineSlot
        ,ProductLine
    FROM (
        SELECT customerID
            ,productLine1
            ,productLine2
            ,productLine3
        FROM tbl
        ) p
    unpivot(ProductLine FOR ProductLineSlot IN (
                productLine1
                ,productLine2
                ,productLine3
                )) AS unpvt
    )
SELECT ProductLine
    ,count(*) AS [Number Of Customers]
FROM Unpivoted
GROUP BY ProductLine;

Or you can rewrite that without CTEs or derived tables:

SELECT
    ProductLine,
    COUNT(*) AS [Number Of Customers]
FROM
    tbl AS t
    UNPIVOT
    (
      ProductLine
      FOR ProductLineSlot IN (productLine1, productLine2, productLine3)
    ) AS u
GROUP BY
    ProductLine
;

To elaborate a little on how the query works, the UNPIVOT clause turns your table into a row set like this:

customerID  ProductLineSlot  ProductLine
----------  ---------------  -----------
1           productLine1     1
2           productLine1     2
2           productLine2     1
3           productLine1     4
3           productLine2     2
3           productLine3     1

Rows where ProductLine is null are automatically omitted by UNPIVOT. You can see that the above row set can be grouped by ProductLine to get row counts per group for the final result:

ProductLine  Number Of Customers
-----------  -------------------
1            3
2            2
4            1

And that is what the query does.

Answer 2

Because COUNT eliminates NULL values, you can use COUNT on each column:

SELECT COUNT([productLine1]) AS [productLine1]
     , COUNT([productLine2]) AS [productLine2]
     , COUNT([productLine3]) AS [productLine3]
FROM tbl

You will get a warning, which is expected. If you'd like to avoid that, you can use SUM with a CASE statement in place of the COUNT.