Solved

Help with Oracle SQL query syntax

Posted on 2014-11-14
16
236 Views
Last Modified: 2014-11-16
Here is the scenario:

-- I have table1 that has one primary key (PK1)
-- Another table called table2 with two primary keys (PK1, LINE#). This table has another column called COL1 that will have values VAL1, VAL2, VAL3 etc. corresponding to LINE# values of 1, 2, 3 and so on

I would like to write a SQL query that will return the following output in one row

PK1,VAL1,VAL2,VAL3...VALN

Can you help me how I can achieve this?
0
Comment
Question by:Newbie345
  • 6
  • 6
  • 3
  • +1
16 Comments
 
LVL 51

Expert Comment

by:Bill Prew
ID: 40444069
I think this should be close to what you want, although I can't test on Oracle right no.

SELECT pk1, LISTAGG(col1, ',') 
WITHIN GROUP (ORDER BY pk1, col1) 
FROM table1
GROUP BY pk1
ORDER BY pk1;

Open in new window

[~bp]
0
 

Author Comment

by:Newbie345
ID: 40444071
Thanks BP. There is not table2 in your query?
0
 
LVL 51

Expert Comment

by:Bill Prew
ID: 40444073
Sorry, in my query table 1 should have been table2.  No need for table1 that I could see.

~bp
0
 

Author Comment

by:Newbie345
ID: 40444090
Hi bp,
I also need table1 in the query as I need to display some other columns from that. Sorry for not mentioning it earlier. So here is the example output format:

table1.PK1,table1.COL1,table1.COL2,table2.VAL1,table2.VAL2,table2.VAL3...table2.VALN
0
 
LVL 31

Expert Comment

by:awking00
ID: 40444516
Can you post some sample data and the expected output?
0
 

Author Comment

by:Newbie345
ID: 40444574
Does this help?

Sample data
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40445045
You cannot dynamically create columns at runtime.  You have to know the number of columns before you run the select.

Do you have a maximum number of orders per customer?

If you want a CSV for order numbers instead of individual columns ,we can work with that.
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40445061
FYI:  images aren't the best way to provide us sample data and results.  It is hard for us to create test cases.

If you have a MAXIMUM of 10 possible orders try something like the code below.

If you have more just change the 10 in "connect by level <= 10" the the maximum and add the appropriate number of new columns:
max(case when t2.pk2 = 10 then order_num end) order10,
...
max(case when t2.pk2 = N then order_num end) orderN


/*
drop table tab1 purge;
create table tab1(pk1 number, name varchar2(10), city varchar2(20), state char(2));

insert into tab1 values(100,'John','LA','CA');
insert into tab1 values(200,'Mike','SD','CA');
insert into tab1 values(300,'Bob','Akron','OH');

drop table tab2 purge;
create table tab2(pk1 number, pk2 number, order_num number);


insert into tab2 values(100,1,100001);
insert into tab2 values(100,2,100002);
insert into tab2 values(100,3,100003);
insert into tab2 values(200,1,200001);
insert into tab2 values(200,2,200002);
insert into tab2 values(300,1,300001);
insert into tab2 values(300,2,300002);
insert into tab2 values(300,3,300003);
insert into tab2 values(300,4,300004);
commit;
*/

select t1.pk1, name, city, state,
	max(case when t2.pk2 = 1 then order_num end) order1,
	max(case when t2.pk2 = 2 then order_num end) order2,
	max(case when t2.pk2 = 3 then order_num end) order3,
	max(case when t2.pk2 = 4 then order_num end) order4,
	max(case when t2.pk2 = 5 then order_num end) order5,
	max(case when t2.pk2 = 6 then order_num end) order6,
	max(case when t2.pk2 = 7 then order_num end) order7,
	max(case when t2.pk2 = 8 then order_num end) order8,
	max(case when t2.pk2 = 9 then order_num end) order9,
	max(case when t2.pk2 = 10 then order_num end) order10
from
tab1 t1,
(
select pk1, pk2, order_num 
from tab2 t2 right outer join ( select level lvl from dual connect by level <= 10) l on t2.pk2 = l.lvl
) t2
where t1.pk1=t2.pk1
group by t1.pk1, name, city, state
order by 1
/

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 51

Expert Comment

by:Bill Prew
ID: 40445119
Give this a try, I think it should do the job.

  SELECT tab2.pk1 || ',' || 
         tab1.name || ',' || 
         tab1.city || ',' ||
         tab1.state || ',' || 
         LISTAGG(tab2.order_num, ',') WITHIN GROUP (ORDER BY tab2.pk1, tab2.order_num) AS lineout
    FROM tab2 INNER JOIN tab1 ON tab1.pk1 = tab2.pk1
GROUP BY tab2.pk1,
         tab1.name,
         tab1.city,
         tab1.state
ORDER BY tab2.pk1,
         tab1.name,
         tab1.city,
         tab1.state;

Open in new window

~bp
0
 

Author Closing Comment

by:Newbie345
ID: 40445198
Works great. Thanks much.
0
 
LVL 51

Expert Comment

by:Bill Prew
ID: 40445200
Just curious, did you give my solution a try, and if so did it not meet your need for some reason?

~bp
0
 

Author Comment

by:Newbie345
ID: 40445207
Hi Bill,
For some reason, I am getting the error below:

ORA-00923: FROM keyword not found where expected
00923. 00000 -  "FROM keyword not found where expected"
*Cause:    
*Action:
Error at Line: 22 Column: 44
0
 
LVL 51

Expert Comment

by:Bill Prew
ID: 40445722
Odd, I just copied the query right out of the solution post above and ran it again here and got the following.

LINEOUT                                                                         
--------------------------------------------------------------------------------
100,John,LA,CA,100001,100002,100003                                             
200,Mike,SD,CA,200001,200002                                                    
300,Bob,Akron,OH,300001,300002,300003,300004                                    

3 rows selected.

Open in new window

No big deal, not complaining or anything, just wanted to share another (slightly simpler?) way to solve the problem.  Make sure you didn't accidentally mis-copy the query of change anything after copying it.  Can you post the exact query you ran there?

No matter what, glad you have something useful from other posts, I just knew I had tested it before posting so was curious if there was a problem.  I'm a curious sort :-)

~bp
0
 

Author Comment

by:Newbie345
ID: 40445748
Sure Bill. I will give this query another try and will let you know.
FYI, I need fixed number of columns (only three order numbers) so # of columns is not dynamic. Can you modify your query to reflect that and let me know?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40446203
Bill's post creates a CSV not individual columns.  If that is what you want then we can address how this question was closed.

If that isn't what you want, then why bother playing with it?

My guess for the reason it won't work for you is listagg was new to 11g.  If you are using an older version of the database, it won't work and you will have to use another method to create the CSV.

I prefer the XMLAGG trick shown here (unless you are running 9i):
http://www.experts-exchange.com/Database/Oracle/Q_24914739.html#a25864822

>>I need fixed number of columns (only three order numbers) so # of columns is not dynamic. Can you modify your query to reflect that and let me know?

For a maximum of 3 columns

Change:
connect by level <= 10

to:
connect by level <= 3

and remove the MAX calls for columns 4-10.
0
 
LVL 51

Expert Comment

by:Bill Prew
ID: 40446221
If you want individual columns then my approach is not preferred, it's useful for combining values from different rows into a single column.  I took that approach because your original question said the following, although it now appears I may have taken that too literally...

I would like to write a SQL query that will return the following output in one row
PK1,VAL1,VAL2,VAL3...VALN
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Stay Alert! 13 62
Cannot open form error 6 48
query in Oracle forms Builder 2 27
SQL Retrieve Values 4 44
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now