Home » SQL & PL/SQL » SQL & PL/SQL » there are two column state and city i want single column output like statewise city .
there are two column state and city i want single column output like statewise city . [message #222742] Tue, 06 March 2007 04:56 Go to next message
victoryhendry
Messages: 95
Registered: January 2007
Location: Bangalore
Member


there are two column state and city i want single column output
like statewise city .i gave some example .

Table: test


state city
------- -------
TamilNadu chennai
TamilNadu dharmapuri
TamilNadu hosur
Karnataka Bangalore
Karnataka K.G.F
Karnataka Mangalore
-----------------------------------

My expecting output
***************************
I want single column output
******************************

State of city
----------------------
TamilNadu chennai
dharmapuri
hosur

Karnataka Bangalore
K.G.F
Mangalore
Re: there are two column state and city i want single column output like statewise city . [message #222745 is a reply to message #222742] Tue, 06 March 2007 05:09 Go to previous messageGo to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
mmmm I probably don't get the real problem because I don't think it's that hard:

select <col A>||','||<col B> from <table>

should give what you want

Or have a look at the documentation (search for concat)
Re: there are two column state and city i want single column output like statewise city . [message #222756 is a reply to message #222742] Tue, 06 March 2007 05:41 Go to previous messageGo to next message
victoryhendry
Messages: 95
Registered: January 2007
Location: Bangalore
Member

No need to use column1 ||column2.
I need like this output


State of city(single column)
----------------------------
TamilNadu chennai
dharmapuri
hosur

Karnataka Bangalore
K.G.F
Mangalore
-----------------------------------


there are two column state and city i want single column output
like statewise city .i gave some example .

Table: test


state city
------- -------
TamilNadu chennai
TamilNadu dharmapuri
TamilNadu hosur
Karnataka Bangalore
Karnataka K.G.F
Karnataka Mangalore
-----------------------------------

My expecting output
***************************
I want single column output
******************************

State of city(single column)
----------------------------
TamilNadu chennai
dharmapuri
hosur

Karnataka Bangalore
K.G.F
Mangalore
Re: there are two column state and city i want single column output like statewise city . [message #222768 is a reply to message #222742] Tue, 06 March 2007 05:54 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Do a search on the forum as martijn mentioned for concat_all

[Updated on: Tue, 06 March 2007 05:54]

Report message to a moderator

Re: there are two column state and city i want single column output like statewise city . [message #222778 is a reply to message #222742] Tue, 06 March 2007 06:05 Go to previous messageGo to next message
lokesh.sql
Messages: 9
Registered: November 2006
Location: Pune
Junior Member
Hi

Select to_char ( state || ‘ ‘|| city) state city from test;


Regards
Lokesh
Re: there are two column state and city i want single column output like statewise city . [message #222779 is a reply to message #222756] Tue, 06 March 2007 06:18 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
@Victoryhendry: You do need to learn that when people ask for clarification of what you want, just reposting your initial request and saying 'I gave some example' isn't terribly helpful.

I can get you the non-repetition of the State column that you are looking for by using the SQL*Plus break command:
SQL> break on state
SQL> with source as (select 'TamilNadu' state,'chennai' city from dual union all
  2                  select 'TamilNadu' state,'dharmapuri' city from dual union all
  3                  select 'TamilNadu' state,'hosur' city from dual union all
  4                  select 'Karnataka' state,'Bangalore' city from dual union all
  5                  select 'Karnataka' state,'K.G.F' city from dual union all
  6                  select 'Karnataka' state,'Mangalore' city from dual)
  7  select * from source;

STATE     CITY
--------- ----------
TamilNadu chennai
          dharmapuri
          hosur
Karnataka Bangalore
          K.G.F
          Mangalore


With a bit of analytic magic, I can get you this:
  1  with source as (select 'TamilNadu' state,'chennai' city from dual union all
  2                  select 'TamilNadu' state,'dharmapuri' city from dual union all
  3                  select 'TamilNadu' state,'hosur' city from dual union all
  4                  select 'Karnataka' state,'Bangalore' city from dual union all
  5                  select 'Karnataka' state,'K.G.F' city from dual union all
  6                  select 'Karnataka' state,'Mangalore' city from dual)
  7  select case when state=prev_state then null
  8              else state
  9              end ||' '||city  state_city
 10  from (select state
 11              ,lag(state) over (partition by state order by city) prev_state
 12              ,city
 13*       from source)
SQL> /

STATE_CITY
--------------------
Karnataka Bangalore
 K.G.F
 Mangalore
TamilNadu chennai
 dharmapuri
 hosur

6 rows selected.
, but it would all be so much easier if you could explain exactly what it was you were looking for.
Previous Topic: escape charecter
Next Topic: fetching values from different columns
Goto Forum:
  


Current Time: Fri Dec 09 21:04:15 CST 2016

Total time taken to generate the page: 0.10441 seconds