Solved

SQL Query

Posted on 2004-09-29
5
1,347 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
  • 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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 information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
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

790 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