Print Page - Oh great SQL wizards, hear my plea

Title: Oh great SQL wizards, hear my plea
Post by: grebo on February 20, 2012, 06:54:50 AM

I'm a SQL newb. Please help me.

Say I have two tables, one has rows like...

1345 Joe Smith
1357 Bob Jones

The other has rows like:

1345 Peanuts
1345 Cheese
1345 Banana
1357 Pencil
1357 Cheese

I wish to do a select whereby I can get rows like

1345 Joe Smith Peanuts Cheese Banana
1357 Bob Jones Pencil Cheese

I believe I need some kind of GROUP BY with an aggregate function or something on the "food" column in the select statement? But my googling is failing me. Help me? Please? CONCAT to an empty variable or some other thing? But I can't get it to work. This is ORACLE SQL if that makes a difference.

Thanks

Title: Re: Oh great SQL wizards, hear my plea
Post by: Miasma on February 20, 2012, 08:02:17 AM

You want data from rows to be forced into an unknown number of columns? I can't really think of a pure sql way to do that, as far as I know you would need a program to format your sql rows for you.

Nearest I can imagine are pivot tables but I've never used them.

Title: Re: Oh great SQL wizards, hear my plea
Post by: MisterNoisy on February 20, 2012, 08:34:37 AM

This article (http://www.dba-oracle.com/t_converting_rows_columns.htm) may help.

Specifically, the following:

select
   table1.number, table1.firstname, table1.lastname,
   rtrim (xmlagg (xmlelement (e, table2.items || ',')).extract ('//text()'), ',') enames
from
   table1 left outer join table2 on table1.number = table2.number
group by
   table1.number, table1.firstname, table1.lastname

That will return the columns from table 1, with the 'items' from table 2 in a single column as a comma-separated string, though. The pivot option should also work for getting everything in it's own column, but is dependent on a newer version of Oracle.

Title: Re: Oh great SQL wizards, hear my plea
Post by: grebo on February 20, 2012, 10:36:54 AM

Hey guys, thx for the replies. I don't know what version of Oracle is in this thing, but the xml stuff doesn't seem to work. I can't even get any sql errors back from it... it's totally like stumbling around in the dark.

I did find a thing on wm_concat which I think I can make do what I need. Only thing is I can't seem to order the results at all... almost there though...

Title: Re: Oh great SQL wizards, hear my plea
Post by: MisterNoisy on February 20, 2012, 10:53:23 AM

Quote from: grebo on February 20, 2012, 10:36:54 AM

You should be able to get the version using this query:

select *
from product_component_version

Title: Re: Oh great SQL wizards, hear my plea
Post by: Salamok on February 20, 2012, 09:53:16 PM

Let me preface this by saying I think this is phenomenally stupid and I would never do it this way but you should be able to achieve what you want using sub queries, using MySQL it would look something like this:

Code:

SELECT a.id, a.first, a.last, b.peanuts, c.cheese, d.banana, e.pencil 
FROM personal_info_table AS a
LEFT JOIN (SELECT person_id, count(item_name) AS peanuts FROM item_table WHERE item_name='peanuts' GROUP BY item_name ORDER BY item_name) AS b ON a.id = b.person_id
LEFT JOIN (SELECT person_id, count(item_name) AS cheese  FROM item_table WHERE item_name='cheese'  GROUP BY item_name ORDER BY item_name) AS c ON a.id = c.person_id
LEFT JOIN (SELECT person_id, count(item_name) AS banana  FROM item_table WHERE item_name='banana'  GROUP BY item_name ORDER BY item_name) AS d ON a.id = d.person_id
LEFT JOIN (SELECT person_id, count(item_name) AS pencil  FROM item_table WHERE item_name='pencil'  GROUP BY item_name ORDER BY item_name) AS e ON a.id = e.person_id

This is a common case where you should grab your data via a single left join then loop through the results in your code to build the data set. For example you could do this:

Code:

SELECT a.id, a.first, a.last, b.item_name
FROM personal_info_table AS a
LEFT JOIN item_table AS b ON a.id = b.person_id
ORDER BY  a.id, b.item_name

Then open the result in excel and do some clever maneuvers with subtotals to get what you want.

edit: so there I was 5 minutes after my post laying in bed about to go to sleep and it occurs to me that I am a complete idiot. I still probably wouldn't do it this way but you can accomplish what I was proposing up top w/o sub queries:

Code:

SELECT a.id, a.first, a.last, SUM(IF(b.item_name='peanuts',1,0)) AS peanuts, SUM(IF(b.item_name='cheese',1,0)) AS cheese, SUM(IF(b.item_name='banana',1,0)) AS banana, SUM(IF(b.item_name='pencil',1,0)) AS pencil
FROM personal_info_table AS a
LEFT JOIN item_table AS b ON a.id = b.person_id
GROUP BY  a.id
ORDER BY  a.id

and of course this gives you a count of each item as opposed to an item list.

Title: Re: Oh great SQL wizards, hear my plea
Post by: Evil Elvis on February 21, 2012, 04:46:32 AM

Quote from: Salamok on February 20, 2012, 09:53:16 PM

... separate joins for each row

That won't work if he has a very large - or unknown - set of values to join in.

If having separate columns isn't a hard requirement, I would suggest using the WITHIN GROUP clause, along with the the LISTAGG() function. You can programmatically split the list into tokens after you get the result set back.

Title: Re: Oh great SQL wizards, hear my plea
Post by: grebo on February 21, 2012, 01:30:54 PM

Thanks guys. yes, the list of options is in the hundreds. And the sql is buried inside a giant web app. I have tried all the ways I can to get the version and none of them seem to work.

I have it pretty much working with that wm_concat unsupported thing, it doesn't sort but oh well. I didn't actually need sql columns, all the data in one column was fine, just needed to be able to stick in delimiters, which I can do.

If I get a chance I'll try that stuff that MisterNoisy posted. I might have been doing it wrong... and working with no error messages or feedback makes second guessing yourself very easy.

Title: Re: Oh great SQL wizards, hear my plea
Post by: Brennik on February 21, 2012, 11:21:43 PM

Oh, so you don't necessarily need a proper rows-to-columns pivot, that makes things slightly easier. SQLSnippets has a variety of rows-to-string methods described at http://www.sqlsnippets.com/en/topic-11787.html (http://www.sqlsnippets.com/en/topic-11787.html). If you're on Oracle 10g, I'd suggest one of the MODEL solutions, since learning to use that clause will probably help you in the long run with other stuff too. If you're on 9, then I'd use the custom STRAGG function one.

But if you want to do a pivot, Tom Kyte has a nice thread on various solutions at http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:766825833740 (http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:766825833740). If you can get your hands on his Expert One-On-One book, there's a ready-made procedure for pivoting in there and I'm pretty sure you can find it "somewhere" even without the book. Tom of course wants to sell you the book ...

f13.net

f13.net General Forums => General Discussion => Topic started by: grebo on February 20, 2012, 06:54:50 AM