Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

write SQL that will give me desirable format

Status
Not open for further replies.

EthanL

Technical User
Mar 22, 2006
3
US
Following SQL gave me the correct result but not desirable format, how can I modify my format so I can have the lay out I want?

Select HSP_ACCOUNT_ID, PAYOR_ID, sum(tx_amount) from hsp_transactions
Where HSP_ACCOUNT_ID=70000958 and (TX_TYPE_HA_C =3 or TX_TYPE_HA_C =4)
Group by HSP_ACCOUNT_ID, PAYOR_ID, TX_TYPE_HA_C;

HSP_ACCOUNT_ID PAYOR_ID SUM(TX_AMOUNT)
70000958 1021 - 476
70000958 1088 -32533.63
70000958 1058 -257.65

Desirable result is one row, if there are three payor, then one row list all three payor by the order of smallest amount to biggest amount.
HSP_ACCOUNT_ID,PAYOR_ID1,SUM(TX_AMOUNT)1,PAYOR_ID2,SUM(TX_AMOUNT)2,PAYOR_ID3,SUM(TX_AMOUNT)3

70000958,1088, -32533.63, 1021, -476, 1058, -257.65

 
You will need to do this in wahtever your reporting front end is. If that is not an option, you will need to use dynamic SQL. How to implement dynamic SQL solution (especially the temp tables I had in mind) will most likely vary between database products, so can you share what database platform you are using?

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom
 
You may find some ideas by searching the Oracle and SQL Server forums for "column" and "row" in the thread title as there have been may solutions written for similar activities. Whether this can be done in ANSI-SQL, I don't know.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top