-
Query Help
I have table (udf) with the following columns and data:
UserID Feildno Value
1 1 Name
1 2 Department
1 3 SSN
1 4 Cost Center
I am trying to construct a query that would allow me to return data in value on separate columns
if I use the following query I get these results
Select userid,value from udf
where fieldno = '1' or fieldno = '3'
UserID Value
1 Name
1 SSN
I need to return only 1 row of data such as
UserID Value Value
1 Name SSN
Any Suggestions?
-
I think you are talking about a pivot query.
This should probably give you what you want.
fyi, I have not tested the it in any way.
Code:
SELECT main.userid, name.name, department.department,
ssn.ssn, costcenter.costcenter
FROM ( SELECT DISTINCT userid
FROM table ) main
INNER JOIN
( SELECT userid, Feildno, Value Name
FROM table ) name
ON name.userid = main.userid
AND name.Feildno = 1
INNER JOIN
( SELECT userid, Feildno, Value department
FROM table ) department
ON department.userid = main.userid
AND department.Feildno = 2
INNER JOIN
( SELECT userid, Feildno, Value ssn
FROM table ) ssn
ON ssn.userid = main.userid
AND ssn.Feildno = 3
INNER JOIN
( SELECT userid, Feildno, Value costcenter
FROM table ) costcenter
ON costcenter.userid = main.userid
AND costcenter.Feildno = 4
ORDER BY main.userid;
-
I would just autojoin the source table like...
Code:
SQL>
SQL> create table udf
2 (userid number,
3 fieldno number,
4 value varchar2(20))
5 ;
Table created.
SQL> insert into udf values(1,1,'Name');
1 row created.
SQL> insert into udf values(1,2,'Department');
1 row created.
SQL> insert into udf values(1,3,'SSN');
1 row created.
SQL> insert into udf values(1,4,'Cost Center');
1 row created.
SQL> commit;
Commit complete.
SQL> select a.userid, a.value, b.value
2 from udf a,
3 udf b
4 where a.userid = b.userid
5 and a.fieldno = 1
6 and b.fieldno = 3
7 ;
USERID VALUE VALUE
---------- -------------------- --------------------
1 Name SSN
SQL>
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Originally Posted by PAVB
I would just autojoin the source table like...
Is that autojoin or cartesean product join??? How does Oracle know that you don't want a cartesean product?
-
there is no need of
a.userid = b.userid to avoid cartesian.
-
Originally Posted by gandolf989
Is that autojoin or cartesean product join??? How does Oracle know that you don't want a cartesean product?
I'm sorry for the confusion, I used the expression autojoin in the sense of joining a table with itself, my fault.
That been said...
Does my solution works? Yes
Does it solves the problem? Yes
Is it both elegant and easy do read? Yes
In that case let me quote the former Chinese PM when he said "...I don't care if the cat is white or black provided the cat catches the mouse"
Last edited by PAVB; 05-02-2008 at 07:55 AM.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Originally Posted by ams-jamali
there is no need of
a.userid = b.userid to avoid cartesian.
Have you tested your idea? do it, it doesn't work.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
here is the code
Code:
SQL> select * from try;
USERID FIELDNO VALUE
---------- ---------- --------------------
1 1 Name
1 2 Department
1 3 SSN
1 4 Cost Center
SQL> select a.userid,a.value,b.value from
2 try a,
3 try b
4 where a.fieldno=1
5 and b.fieldno=3;
USERID VALUE VALUE
---------- -------------------- --------------------
1 Name SSN
Thanks
ams-jamali
-
here the idea is your's.., gandolf989 Quoted so i told that..,
-
Originally Posted by ams-jamali
here is the code
Your code appears to be working just because of all rows in your table have userid=1, cant' you see that?
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|