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
Best Answer
Try this ( replace
tbl
with your table name):Or you can rewrite that without CTEs or derived tables:
To elaborate a little on how the query works, the UNPIVOT clause turns your table into a row set like this:
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:
And that is what the query does.