按具有相同层次结构的不同成员的多个列分组？

Question

我在这样的表结构中有客户数据：

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

每个客户被分配到至少一条生产线，但最多有三条生产线。

现在我想看看每条生产线我有多少客户。对如何解决这个问题有什么想法吗？

重要事项:我不希望将列转换为行(这将是一个解决方案)。因此，结果将是：

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

Answer 1

尝试以下操作(用您的表名替换tbl )：

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;

或者您可以在不使用CTE或派生表的情况下重写：

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

为了详细说明查询的工作方式，UNPIVOT子句将表转换为如下所示的行集：

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

UNPIVOT自动省略ProductLine为null的行。您可以看到，上面的行集可以由ProductLine分组，以获得最终结果的每个组的行数：

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

这就是查询所做的。

Answer 2

因为COUNT消除了空值，所以可以在每一列上使用COUNT：

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

你会收到警告的，这是预期的。如果您想避免这种情况，可以使用SUM使用一个CASE声明代替COUNT。