Home » SQL & PL/SQL » SQL & PL/SQL » How to get desire output
How to get desire output [message #616334] Mon, 16 June 2014 00:10 Go to next message
aaditya321
Messages: 225
Registered: January 2014
Location: Delhi
Senior Member
How to get desire output in sql query.
Need desire output:-

Rajesh Singh
Suresh Pandey
Raj Kumar Pratap


Here in first row where space should be 1 in between first name & last name
second row where space should be 2 in between first name & last name
third row where space should be 1 in middle & last name.
Re: How to get desire output [message #616336 is a reply to message #616334] Mon, 16 June 2014 00:14 Go to previous messageGo to next message
Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Concatenate first and last name with one or two spaces in between.

That's somewhat foggy requirement. How do you know which row is the first one? What makes "Rajesh Singh" the first? How are we supposed to know that? Which rule leads us that there should be one space between "Rajesh" and "Singh", while "Suresh Pandey" require two spaces?
Re: How to get desire output [message #616339 is a reply to message #616336] Mon, 16 June 2014 00:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

... what is the Oracle version? what is the input? what is the output?...

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

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.
Re: How to get desire output [message #616345 is a reply to message #616336] Mon, 16 June 2014 01:19 Go to previous messageGo to next message
aaditya321
Messages: 225
Registered: January 2014
Location: Delhi
Senior Member
Littlefoot wrote on Mon, 16 June 2014 00:14
Concatenate first and last name with one or two spaces in between.

That's somewhat foggy requirement. How do you know which row is the first one? What makes "Rajesh Singh" the first? How are we supposed to know that? Which rule leads us that there should be one space between "Rajesh" and "Singh", while "Suresh Pandey" require two spaces?



Rajesh is First Name and Singh is last name then space should be 1 between first and last name.
Suresh is first name and pandey is last name then now space should be 2 between first and last name.
Re: How to get desire output [message #616346 is a reply to message #616345] Mon, 16 June 2014 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You repeat, you don't explain nor answer Littlefoot's questions.

In addition, you did neither answer my questions nor post what is requested.


Re: How to get desire output [message #616347 is a reply to message #616346] Mon, 16 June 2014 01:27 Go to previous messageGo to next message
Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If that's so, no problem:
SQL> WITH test AS (SELECT 'Rajesh' first_name, 'Singh' last_name FROM DUAL
  2                UNION ALL
  3                SELECT 'Suresh', 'Pandey' FROM DUAL
  4                UNION ALL
  5                SELECT 'Raj Kumar', 'Pratap' FROM DUAL)
  6  SELECT first_name
  7         || CASE
  8               WHEN first_name = 'Rajesh' AND last_name = 'Singh' THEN ' '
  9               WHEN first_name = 'Suresh' AND last_name = 'Pandey' THEN '  '
 10            END
 11         || last_name
 12            result
 13    FROM test;

RESULT
-----------------
Rajesh Singh
Suresh  Pandey
Raj KumarPratap

SQL>
Re: How to get desire output [message #616351 is a reply to message #616347] Mon, 16 June 2014 02:11 Go to previous messageGo to next message
aaditya321
Messages: 225
Registered: January 2014
Location: Delhi
Senior Member
Thank you Littlefoot, I appreciated your kind of help.
Re: How to get desire output [message #616352 is a reply to message #616347] Mon, 16 June 2014 02:12 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Littlefoot wrote on Mon, 16 June 2014 11:57
Raj KumarPratap


For 3rd row, OP wants

aaditya321 wrote on Mon, 16 June 2014 10:40
third row where space should be 1 in middle & last name.


A space between "Kumar" and "Pratap"

aaditya321 wrote on Mon, 16 June 2014 10:40
second row where space should be 2 in between first name & last name

@OP, how to know which is the first, middle or last name? If there is any rule, then the query can handle all the rows. Else, you have to hardcode each name.
Re: How to get desire output [message #616355 is a reply to message #616352] Mon, 16 June 2014 02:30 Go to previous messageGo to next message
aaditya321
Messages: 225
Registered: January 2014
Location: Delhi
Senior Member
Here Raj is first name, Kumar is middle name and Pratap is last name where 1 space should be middle name and last name in third row.

[Updated on: Mon, 16 June 2014 02:30]

Report message to a moderator

Re: How to get desire output [message #616356 is a reply to message #616355] Mon, 16 June 2014 02:40 Go to previous messageGo to next message
Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I know that Kumar and Pratap were sticked together. I did that intentionally, trying to point out (unsuccessfully) futility of the "explanation" aaditya321 provided. Until he realizes and understands what we are saying, this discussion is useless.
Re: How to get desire output [message #616358 is a reply to message #616355] Mon, 16 June 2014 02:49 Go to previous message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What is the result for "Michel" "Cadot" and "Little" "foot"? Why?

Previous Topic: function
Next Topic: how to recover data in case of truncate table and drop partition
Goto Forum:
  


Current Time: Fri Apr 19 15:29:33 CDT 2024