CODEWITHSUNDEEP
sql-server
Calgar99
Calgar99

Reputation: 1688

SQL pivot table without using pivot function

I need to convert a table from a standard table into a more summarized version. The rows should be based whether its the year 2013 or 2014. I was considering using a pivot but I don't believe its necessary. The code below fails because of the group by function of a column Ive just created is there a way to get around this?

CODE

CREATE VIEW -- select * from 
    [03_rdm].[AR_LedgerByInvoice] 
as
select [EY_Business_Unit]
        ,(Case when [AR_Fiscal Year] = 2014 then 'AR_Outstanding_Amount (CY)' else 'AR_Outstanding_Amount (PY)' end) [Aging_Bucket]
        , [AR_1-30 Days Past Due]
        , [AR_31-60 Days Past Due]
        , [AR_61-90 Days Past Due]
FROM [02_cdm].[0040_AR_InvoiceSummary_2013_2014]
GROUP BY [Aging_Bucket]

The end result should be:

DESIRED OUTPUT

Business Unit   Aged Bucket                [AR_1-30 Days]   [AR_31-60 Days] [AR_61-90 Days] Total
100 `           AR_Outstanding_Amount (CY)  x                xx             xxx             xxxx
100             AR_Outstanding_Amount (PY)  x                xx             xxx             xxxx

Upvotes: 0

Views: 530

Answers (1)

jpw
jpw

Reputation: 44911

You can't group by an alias created on the same 'level' but you should be able to group by the same expression, but maybe you just want to group by year? (if so maybe GROUP BY [AR_Fiscal Year] is what you really want):

CREATE VIEW -- select * from 
    [03_rdm].[AR_LedgerByInvoice] 
as
select [EY_Business_Unit]
        ,(Case when [AR_Fiscal Year] = 2014 then 'AR_Outstanding_Amount (CY)' else 'AR_Outstanding_Amount (PY)' end) [Aging_Bucket]
        , [AR_1-30 Days Past Due]
        , [AR_31-60 Days Past Due]
        , [AR_61-90 Days Past Due]
FROM [02_cdm].[0040_AR_InvoiceSummary_2013_2014]
GROUP BY 
  [EY_Business_Unit],
  CASE WHEN [AR_Fiscal Year] = 2014 THEN 'AR_Outstanding_Amount (CY)' ELSE 'AR_Outstanding_Amount (PY)' END

As you didn't provide any sample data or table I couldn't test it, but it should work.

Or maybe you want to aggregate the rows:

select [EY_Business_Unit]
        ,(Case when [AR_Fiscal Year] = 2014 then 'AR_Outstanding_Amount (CY)' else 'AR_Outstanding_Amount (PY)' end) [Aging_Bucket]
        , SUM([AR_1-30 Days Past Due])
        , SUM([AR_31-60 Days Past Due])
        , SUM([AR_61-90 Days Past Due])
FROM [0040_AR_InvoiceSummary_2013_2014]
GROUP BY 
[EY_Business_Unit]
        ,(Case when [AR_Fiscal Year] = 2014 then 'AR_Outstanding_Amount (CY)' else 'AR_Outstanding_Amount (PY)' end)
order by [EY_Business_Unit];

Upvotes: 3

Related Questions