SQL Query

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

Who is Participating?
catchmeifuwantConnect With a Mentor Commented:
Use this :

select name,max(ID_no),max(S_No)
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
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;

---------- ---------- ----------
Jane            73494      98234
Joe             12345     873498

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
baonguyen1Connect With a Mentor Commented:
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
geotigerConnect With a Mentor Commented:
Or use CASE

select name,max(ID_no),max(S_No)
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;
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.