行到列和分组

Question

我有一个查询，看起来像这样：

SELECT OrganizationName, OrganizationID, ReceivableStatus, InvoiceFee
FROM v_InvoicesFreelanceOutstanding
ORDER BY OrganizationID

来自该数据的数据可能看起来像这样：

OrganizationName    OrganizationID        ReceivableStatus       InvoiceFee
-----------------------------------------------------------------------------
Company A                      139        60-90 days                 672.00
Company A                      139        60-90 days                1800.00
Company A                      139        over 90 days              1440.00
Company B                      264        Current                   3559.38
Company B                      264        60-90 days                3785.50
Company C                      271        60-90 days                 446.25
Company C                      271        over 90 days               637.50
Company C                      271        over 90 days              1126.25

我最终想要显示的是这样的（对于上面的数据）：

Company     Current    30-60 days    60-90 days    over 90 days       Total   
-----------------------------------------------------------------------------
Company A         0             0       2472.00               0     2472.00
Company B   3559.38             0       3785.50               0     7344.88
Company C         0             0        446.25         1763.75     2210.00

我的SQL-fu不足以使我超越此范围：

SELECT
    MAX(OrganizationName) as OrganizationName,
    OrganizationID,
    ReceivableStatus,
    SUM(InvoiceFee) as TotalDue
FROM v_InvoicesFreelanceOutstanding
GROUP BY OrganizationID, ReceivableStatus

上面显示了这样的内容（同样，来自上面的数据）：

OrganizationName    OrganizationID        ReceivableStatus         TotalDue
-----------------------------------------------------------------------------
Company A                      139        60-90 days                2472.00
Company A                      139        over 90 days              1440.00
Company B                      264        Current                   3559.38
Company B                      264        60-90 days                3785.50
Company C                      271        60-90 days                 446.25
Company C                      271        over 90 days              1763.75

然后怎样呢？任何帮助，将不胜感激。

需要注意的是在第二表中所示的状态（Current，30-60 days，60-90 days，over 90 days）是唯一我期待拿出下ReceivableStatus。

编辑 ：对不起，不包括此。我知道，PIVOT但我无法让它做我想做的事。

Answer 1

pivot如果您使用的是SQL
Server 2005或更高版本，这看起来像是一项工作。

编辑：

既然您已经了解pivot，您就会知道它几乎可以满足您的需求。

您已经有以下查询：

SELECT
    MAX(OrganizationName) as OrganizationName,
    OrganizationID,
    ReceivableStatus,
    SUM(InvoiceFee) as TotalDue
FROM v_InvoicesFreelanceOutstanding
GROUP BY OrganizationID, ReceivableStatus

这样可以为您提供当前所需的30-60、60-90和90+零件。如果您做到这一点，那么除了总数之外，您将获得所需的一切。因此，只需投入总计：

(SELECT
    MAX(OrganizationName) as OrganizationName,
    OrganizationID,
    ReceivableStatus,
    SUM(InvoiceFee) as TotalDue
FROM v_InvoicesFreelanceOutstanding
GROUP BY OrganizationID, ReceivableStatus)
UNION
(SELECT
    MAX(OrganizationName) as OrganizationName,
    OrganizationID,
    'Total' AS ReceivableStatus,
    SUM(InvoiceFee) as TotalDue
FROM v_InvoicesFreelanceOutstanding
GROUP BY OrganizationID)

透视此结果，您将获得所需的输出：

SELECT *
FROM 
    [the query above]
PIVOT (
    SUM(TotalDue)
    FOR ReceivableStatus IN ([Current],[30-60 days],[60-90 days],[over 90 days],[Total]) 
)