CODEWITHSUNDEEP
sqlsql-server
Paolo Bergantino
Paolo Bergantino

Reputation: 488384

Rows Into Columns and Grouping

I have a query that looks like this:

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

The data from that might look like this:

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

What I want to eventually display is something like this (for the data above):

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

My SQL-fu is not enough to get me past this:

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

Which shows something like this (again, from the data above):

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

What then? Any help would be appreciated.

Note that the statuses shown in the 2nd table (Current, 30-60 days, 60-90 days, over 90 days) are the only ones I'm expecting to come up under ReceivableStatus.

EDIT: Sorry for not including this. I am aware of PIVOT but I couldn't get it to do what I want.

Upvotes: 11

Views: 35115

Answers (5)

Christopher Klein
Christopher Klein

Reputation: 2793

Well, here is another pivot :)

SET NOCOUNT ON
    
DECLARE @table TABLE
(   OrganizationName    VARCHAR(20),
    OrganizationID      INT,
    ReceivableStatus    VARCHAR(20),
    InvoiceFee      FLOAT
)

INSERT INTO @table
SELECT 'Company A',139,'60-90 days',672.00 UNION
SELECT 'Company A',139,'60-90 days',1800.00 UNION
SELECT 'Company A',139,'over 90 days',1440.00 UNION
SELECT 'Company B',264,'Current',3559.38 UNION
SELECT 'Company B',264,'60-90 days',3785.50 UNION
SELECT 'Company C',271,'60-90 days',446.25 UNION
SELECT 'Company C',271,'over 90 days',637.50 UNION
SELECT 'Company C',271,'over 90 days',1126.25

--Specify Just the fields you want to return
;WITH COMPANYINFO(OrganizationName,OrganizationID,ReceivableStatus,InvoiceFee) AS
    (
    SELECT  OrganizationName,
        OrganizationID,
        ReceivableStatus,
        InvoiceFee
    FROM    @Table AS b 
    )
    SELECT * 
    FROM COMPANYINFO
    PIVOT
        (
        SUM(InvoiceFee)
        FOR ReceivableStatus
        IN ([Current],[60-90 days],[over 90 days])
        )
    AS P
    ORDER BY OrganizationName

Upvotes: 2

adolf garlic
adolf garlic

Reputation: 3096

PIVOT sucks. It has horrible syntax and isn't a PIVOT in the pivot table sense i.e. you have to know exactly how many columns will result in advance.

It's probably easier to do a cross-tab report.

SELECT 
OrganizationName,
OrganizationID,
SUM(CASE WHEN ReceivableStatus       = '30-60 days' THEN InvoiceFee ELSE 0 END) AS [30 - 60 Days],
SUM(CASE WHEN ReceivableStatus       = '60-90 days' THEN InvoiceFee ELSE 0 END) AS [60 - 90 Days],
SUM(CASE WHEN ReceivableStatus       = '90-120 days' THEN InvoiceFee ELSE 0 END) AS [90 - 120 Days]

FROM
v_InvoicesFreelanceOutstanding
GROUP BY OrganizationID

Upvotes: 8

Welbog
Welbog

Reputation: 60398

This looks like a job for pivot if you're using SQL Server 2005 or later.

EDIT:

Since you already know about pivot, you know it does almost what you need.

You already have the following query:

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

Which gives you the current, 30-60, 60-90 and 90+ parts that you need. If you pivot that, you get everything you need except for your total. So just throw in the total:

(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)

Pivot on this result and you should get the output you want:

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

Upvotes: 4

AdamB
AdamB

Reputation: 9100

I have not used pivot before, so that may be your answer, but I've used brute force on a problem like this in the past and for you it would look something like:

SELECT OrganizationName, SUM(Current) AS Current, SUM(3060Days) AS 3060Days, SUM(6090Days) AS 6090Days, SUM(Over90Days) As Over90Days, SUM(Total) AS Total
FROM 
(
SELECT 
    OrganizationName,
    CASE WHEN ReceivableStatus = 'Current' THEN
        SUM(InvoiceFee)
    ELSE
        0
    END AS Current,
    CASE WHEN ReceivableStatus = '30-60 days' THEN
        SUM(InvoiceFee)
    ELSE
        0
    END AS 3060Days,
    CASE WHEN ReceivableStatus = '60-90 days' THEN
        SUM(InvoiceFee)
    ELSE
        0
    END AS 6090Days,
    CASE WHEN ReceivableStatus = 'over 90 days' THEN
        SUM(InvoiceFee)
    ELSE
        0
    END AS Over90Days,
    SUM(InvoiceFee) AS Total
FROM v_InvoicesFreelanceOutstanding
GROUP BY OrganizationName, ReceivableStatus) temp
GROUP BY OrganizationName

Basically, the inner query gives you results for each organization and a total for each individual category and the outer query aggregates all of them into single rows for each organization. Again, this may not be the most elegant or efficient way to go, but it is a way.

Upvotes: 2

Gustavo
Gustavo

Reputation: 359

I think your are searching for the pivot and unpivot functionality. Take a look at this: http://msdn.microsoft.com/en-us/library/ms177410.aspx

Upvotes: 0

Related Questions