| How To Show Column Value separated with colon as well as another column vlaue [message #677287] |
Tue, 10 September 2019 00:06  |
 |
ssyr
Messages: 65
Registered: January 2017
|
Member |
|
|
Hello all,
I need some help on displaying column value separated with ',' and ':' at the same time I have to display another column value with First column value.
Ex.My table values are-
ID TX_PARTS TX_AMT
309336742 GIFTS 6.99
309336742 TOYS 4.99
309336742 PRINT 4.49
309336742 ZBOX 4.5
I have to show result as:
ID Values
309336742 GIFTS:6.99 ,TOYS:4.99 ,PRINT:4.49,ZBOX:4.5
My Query:
[CODE]
SELECT id a ,LISTAGG (TX_PARTS,TX_AMT, ':')
WITHIN GROUP (ORDER BY TX_PARTS TX_AMT desc )Breakdown
FROM transaction_parts pt
GROUP BY id
{/CODE]
But above query does not work.
Could you please help me out on this issue.
|
|
|
|
|
|
|
|
| Re: How To Show Column Value separated with colon as well as another column vlaue [message #677291 is a reply to message #677290] |
Tue, 10 September 2019 00:52  |
flyboy
Messages: 1901
Registered: November 2006
|
Senior Member |
|
|
Just a suggestion.
LISTAGG function has two parameters.
The second one is the delimiter separating individual row values. It is comma in the expected result, so it shall be comma (',') in the constructed SQL as well.
The first parameter is measured expression. Value "GIFTS:6.99 " representing the first row. Just construct that expression from respective columns and constant string(s) concatenated with concatenation operator (||) and use it there.
Another explanation: the colon (':') is not a separator of table rows - it is just of one part of individual row value.
|
|
|
|
|
|
| Re: How To Show Column Value separated with colon as well as another column vlaue [message #677295 is a reply to message #677294] |
Tue, 10 September 2019 09:07 |
 |
JPBoileau
Messages: 88
Registered: September 2017
|
Member |
|
|
here:
with mydata as (
select 309336742 as ID, 'GIFTS' as tx_parts, 6.99 as tx_amt from dual union all
select 309336742, 'PRINT', 4.99 from dual union all
select 999999999, 'OTHER', 1.99 from dual union all
select 309336742, 'ZBOX', 4.5 from dual )
select id,
LISTAGG(tx_parts || ': ' || to_char(tx_amt), ', ') WITHIN GROUP (order by tx_parts) as list_parts
from mydata
group by id;
ID LIST_PARTS
---------- ----------------------------------------
309336742 GIFTS: 6.99, PRINT: 4.99, ZBOX: 4.5
999999999 OTHER: 1.99
2 rows selected.
JP
[Updated on: Tue, 10 September 2019 09:08] Report message to a moderator |
|
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
