Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Query

Posted on 2004-09-29
5
Medium Priority
?
1,352 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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

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…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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.

609 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