Home » SQL & PL/SQL » SQL & PL/SQL » How To Show Column Value separated with colon as well as another column vlaue (Oracle 11G)
How To Show Column Value separated with colon as well as another column vlaue [message #677287] Tue, 10 September 2019 00:06 Go to next message
ssyr
Messages: 64
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 #677288 is a reply to message #677287] Tue, 10 September 2019 00:17 Go to previous messageGo to next message
Michel Cadot
Messages: 66654
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

From your previous topic;

Michel Cadot wrote on Fri, 30 August 2019 15:17

...
Also feed back in your previous topics.
Re: How To Show Column Value separated with colon as well as another column vlaue [message #677290 is a reply to message #677288] Tue, 10 September 2019 00:23 Go to previous messageGo to next message
ssyr
Messages: 64
Registered: January 2017
Member
Sure Michel ,

Next time onward I will follow rules.

I thanks to all who has suggested me the solution.
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 Go to previous messageGo to next message
flyboy
Messages: 1880
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 #677294 is a reply to message #677287] Tue, 10 September 2019 07:35 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2884
Registered: January 2010
Location: Connecticut, USA
Senior Member
LISTAGG (TX_PARTS || ':' || TX_AMT,',')

SY.
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 Go to previous messageGo to next message
JPBoileau
Messages: 75
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

Re: How To Show Column Value separated with colon as well as another column vlaue [message #677311 is a reply to message #677295] Wed, 11 September 2019 07:14 Go to previous message
ssyr
Messages: 64
Registered: January 2017
Member
Hi All,

Thank you all for your feed back.

Thank you all for your help on this issue. Smile
Previous Topic: display data in new column
Next Topic: View not pulling with updated table information
Goto Forum:
  


Current Time: Wed Oct 23 16:00:04 CDT 2019