SQL query customized output

Question

I have below query:

   select 'my.MYNAME=' + name  from hostnames;

   my.MYNAME=abc
   my.MYNAME=xyz
   my.MYNAME=poi

The query is dynamic is gives3 result, it may give more result depending upon data.

I need following output:

my.MYNAME1=abc
my.MYNAME2=xyz
my.MYNAME3=poi

Numbers appending to MYNAME according to result it gives.

I have tried

select 'my.MYNAME={c}' + name  from hostnames where (select count(*) as c from name);

but it is not working.

Just append ROWNUM after MYNAME

Kaushik Nayak
– Kaushik Nayak

2018-05-16 15:03:24 +00:00
Commented May 16, 2018 at 15:03 — Kaushik Nayak
– Kaushik Nayak, Commented May 16, 2018 at 15:03

justMe · Accepted Answer · 2018-05-16 15:29:29Z

3

One way to go about it is:

SELECT CONCAT(CONCAT(CONCAT('my.MYNAME',ROWNUM),'='), name) FROM hostnames

DEMO

edited May 16, 2018 at 15:29

answered May 16, 2018 at 15:18

justMe

2,47818 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alex Poole Over a year ago

It's a bit simpler and easier to follow with the concatenation operator?

justMe Over a year ago

Hi Alex, I agree it is simpler but according to Doc When moving SQL script files between systems having different character sets, such as between ASCII and EBCDIC, vertical bars might not be translated into the vertical bar required by the target Oracle Database environment.

Collectives™ on Stack Overflow

SQL query customized output

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related