August 9, 2011 at 10:02 am
I have a table of data for which I need to make some of the columns separate rows, but I do not want to aggregate any data. Currently the table stores 4 amounts in a separate field, one row per invoice.
CustomerIDDocNumDocDateDueUsDueYouTotalCollectionsFeeIncome
SAL0002 10541817/27/20117638.170-9919.7-2281.53
SAL0003 10541837/27/20111247.20-1708.5-461.3
SAL0004 10541857/27/2011577.50-825-247.5
However, I need to show 4 rows per invoice:
CustomerIDDocNumDocDateAccountAmount
SAL0002 10541817/27/2011DueUs7638.17
SAL0002 10541817/27/2011DueYou0
SAL0002 10541817/27/2011TotalCollections-9919.7
SAL0002 10541817/27/2011FeeIncome-2281.53
SAL0003 10541837/27/2011DueUs1247.2
SAL0003 10541837/27/2011DueYou0
SAL0003 10541837/27/2011TotalCollections-1708.5
SAL0003 10541837/27/2011FeeIncome-461.3
SAL0004 10541857/27/2011DueUs577.5
SAL0004 10541857/27/2011DueYou0
SAL0004 10541857/27/2011TotalCollections-825
SAL0004 10541857/27/2011FeeIncome-247.5
I'm confusing myself with this and could really use some assistance. Attached is some code to create a table with sample data.
Thanks!
August 9, 2011 at 11:17 am
How about just using Union All operations?
select CustomerID, DocNum, DocDate, 'Due Us' as Acct, DueUs as Ammt
from YourTable
Union All
select CustomerID, DocNum, DocDate, 'Due You' as Acct, DueYou
from YourTable
Union All
select CustomerID, DocNum, DocDate, 'Total Collections' as Acct, TotalCollections
from YourTable
Union All
select CustomerID, DocNum, DocDate, 'Fee Income' as Acct, FeeIncome
from YourTable
Order By CustomerID, DocNum, DocDate;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 9, 2011 at 11:24 am
Fantastic! I knew I was trying to make it too complicated. Thanks so much for your help!
Trish
August 10, 2011 at 6:38 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply