Solved

SQL Query

Posted on 2004-09-29
5
1,350 Views
Last Modified: 2008-01-09
I have been trying to write a SQL query. The problem I have with this is I want to show the rows as a columns. My database details clients ID number and a serial number as seperate rows, however, I want it show them as a column - is there a way to do this?

The database has the structure shown below as there are several other types of numbers but I am specifically looking at these two types only.

Currently shows,

Name            Type                Number

Joe Bloggs     ID Number       123456
Joe Bloggs     Serial Number   789101
Jane Bloggs   ID Number        543210
Jane Bloggs   Serial Number    987654

I want to be

Name           ID No          Serial Number

Joe Bloggs   123456     789101
Jane Bloggs  543210    987654

Thanks
0
Comment
Question by:leigh_richardson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 12

Accepted Solution

by:
catchmeifuwant earned 300 total points
ID: 12178055
Use this :

select name,max(ID_no),max(S_No)
from
(
select name,decode(type,'ID Number',Number,NULL) as ID_No,decode(type,'Serial Number',Number,NULL) as S_No
from mytable
)
group by name
0
 
LVL 12

Expert Comment

by:catchmeifuwant
ID: 12178060
Here's an example:


SQL> select * from temp;

NAME       TYPE             S_NO
---------- ---------- ----------
Joe        ID              12345
Joe        Serial         873498
Jane       ID              73494
Jane       Serial          98234

SQL> select name,max(ID_no),max(S_No)
  2  from
  3  (
  4  select name,decode(type,'ID',S_NO,NULL) as ID_No,decode(type,'Serial',S_NO,NULL) as S_No
  5  from temp
  6  )
  7  group by name;

NAME       MAX(ID_NO)  MAX(S_NO)
---------- ---------- ----------
Jane            73494      98234
Joe             12345     873498

SQL>
-------------------------
0
 
LVL 12

Expert Comment

by:catchmeifuwant
ID: 12178069
Or simply...

select name,max(decode(type,'ID',S_NO,NULL)) as ID_No,max(decode(type,'Serial',S_NO,NULL)) as S_No
from temp
group by name
0
 
LVL 8

Assisted Solution

by:baonguyen1
baonguyen1 earned 150 total points
ID: 12178924
or you can try:

SQL> select * from t12;

NAME       TYPE       NUM
---------- ---------- ----------
Joe Bloggs ID Number  123456
Joe Bloggs Serial Num 789101
JaneBloggs ID Number  543210
JaneBloggs Serial Num 987654

SQL> ed
Wrote file afiedt.buf

  1   select distinct c.name , a.num as "ID No", b.num as "Serial No" from
  2  (select name, type, num from t12 where type like 'ID%') a,
  3  (select name, type, num from t12 where type like 'Se%') b,
  4  t12 c
  5  where c.name = a.name(+)
  6* and c.name= b.name(+)
SQL> /

NAME       ID No      Serial No
---------- ---------- ----------
JaneBloggs 543210     987654
Joe Bloggs 123456     789101

Hope this helps
0
 
LVL 12

Assisted Solution

by:geotiger
geotiger earned 50 total points
ID: 12179373
Or use CASE

select name,max(ID_no),max(S_No)
from
(
select name, 9case when type ='ID' then S_NO else null end) as ID_No,
         (case when type ='Serial' then S_NO else NULL end) as S_No
from temp
)
group by name;
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question