Geek Answers Handbook

Rows to 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 this may 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 ultimately display looks 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 pass me 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 above data):

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

? .

, , 2- (Current, 30-60 days, 60-90 days, over 90 days), , ReceivableStatus.

: , . PIVOT, , .

+5
5

pivot, SQL Server 2005 .

EDIT:

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]) 
)
+2

PIVOT . PIVOT , .. , .

, -.

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
+3

, :)

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
+2

pivot, , , :

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

, . , , .

+1

More articles:


All Articles