?
Solved

SQL Query

Posted on 2004-09-29
5
Medium Priority
?
1,351 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 1200 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 600 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 200 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Suggested Courses

743 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