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
  • But your sample output is rows – paparazzo Jun 27 '16 at 10:05

Try this ( replace tbl with your table name):

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

Or you can rewrite that without CTEs or derived tables:

    COUNT(*) AS [Number Of Customers]
    tbl AS t
      FOR ProductLineSlot IN (productLine1, productLine2, productLine3)
    ) AS u

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.


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.