Newbie345
asked on
Help with Oracle SQL query syntax
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?
-- 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?
ASKER
Thanks BP. There is not table2 in your query?
Sorry, in my query table 1 should have been table2. No need for table1 that I could see.
~bp
~bp
ASKER
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,tab le1.COL2,t able2.VAL1 ,table2.VA L2,table2. VAL3...tab le2.VALN
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,tab
Can you post some sample data and the expected output?
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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;
~bp
ASKER
Works great. Thanks much.
Just curious, did you give my solution a try, and if so did it not meet your need for some reason?
~bp
~bp
ASKER
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
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
Odd, I just copied the query right out of the solution post above and ran it again here and got the following.
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
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.
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
ASKER
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?
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?
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):
https://www.experts-exchange.com/questions/24914739/In-Oracle-how-can-I-concatenate-multiple-results-returned-from-select-statement-into-a-comma-separated-string.html?anchorAnswerId=25864822#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.
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):
https://www.experts-exchange.com/questions/24914739/In-Oracle-how-can-I-concatenate-multiple-results-returned-from-select-statement-into-a-comma-separated-string.html?anchorAnswerId=25864822#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.
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
Open in new window
[~bp]