LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Programming (http://www.linuxquestions.org/questions/programming-9/)
-   -   multiple select SQL more direct way to write it (http://www.linuxquestions.org/questions/programming-9/multiple-select-sql-more-direct-way-to-write-it-4175472264/)

bop-a-nator 08-05-2013 10:18 PM

multiple select SQL more direct way to write it
 
I created a table I'll call Measurements to illustrate what I was trying to do, it is simplier to look at then my "real" data. It has three columns:

Item Dimension Value
A1 Len 3
A1 Wdth 4
A1 Hgth 5
A2 Len 2
A2 Wdth 7
A2 Hgth 1
A3 Len 6
A3 Wdth 6
A3 Hgth 6
.
.
.
Nth Item

What I want is output:

Item Length Width Height
A1 3 4 5
A2 2 7 1
A3 6 6 6
.
.
.
Nth Item


I wrote this then thought, there has got to be some other way to write this:

select unique p1.Item, (select Dimension from Measurements p2 where p1.Item = p2.Item
and p2.Dimension = 'Len) as "Length", (select value from Measurements p3 where p1.Item = p3.Item
and p3.Dimension = 'Wdth') as "Width", (select value from Measurements p4 where p1.Item = p4.Item
and p4.Dimension = 'Hgth') as "Height", from Measurements p1
/

I imagine this is basic stuff, and I have no fancy SQL coding tool just a text pad.

Thanks for your help
bop-a-nator

grail 08-05-2013 11:28 PM

Is the desire to be able to do this in SQL or to have the data formatted as output to a file / screen?

If the latter then a simple awk based on splitting the data on every third line is a doddle.

Note: I would generally mention which type of SQL (mysql, access, oracle) you are using as some functions are not transferable.

astrogeek 08-06-2013 12:00 AM

Unless I am missing something...

[SNIP]

... I was missing something - see next post...

astrogeek 08-06-2013 01:06 AM

This should work for any SQL99 RDBMs I think
Code:

SELECT
Item,
SUM(CASE WHEN Dimension='Len' THEN Value ELSE 0 END) as 'Length',
SUM(CASE WHEN Dimension='Wdth' THEN Value ELSE 0 END) as 'Width',
SUM(CASE WHEN Dimension='Hgth' THEN Value ELSE 0 END) as 'Height'
FROM Measurements
GROUP BY Item ORDER BY Item


bop-a-nator 08-06-2013 10:42 AM

I am using plain SQL into an Oracle DB. As I also have to join to another table to pull some other data, too. I tried the case statement provided it is certainly simplier then what I had started with and I was able to add the join I needed. astrogeek certainly give you credit for sharing the usage of the case type of statement.

I know the DBAs aren't fond of multiple "select" statements like the one I had shown above and I wanted to avoid building a temp table if I didn't need to.

Just for curiousity and it is always good to learn more then one way to do something, could this be done by also joining the table to itself?

astrogeek 08-06-2013 03:00 PM

Quote:

Originally Posted by bop-a-nator (Post 5004047)
I am using plain SQL into an Oracle DB. As I also have to join to another table to pull some other data, too. I tried the case statement provided it is certainly simplier then what I had started with and I was able to add the join I needed. astrogeek certainly give you credit for sharing the usage of the case type of statement.

I know the DBAs aren't fond of multiple "select" statements like the one I had shown above and I wanted to avoid building a temp table if I didn't need to.

Just for curiousity and it is always good to learn more then one way to do something, could this be done by also joining the table to itself?

"Could it be done..." that way, sure, but at the expense of performance and memory usage - and promoting some generally bad habits. ;)

The key to all of it is to understand the relational model and to "think in sets".

That is a simple concept actually, but not easily communicated in a forum. As that applies to this problem I would suggest that the multi-join, sub-select query structure results from trying to see it all procedurally - one thing after another like a programming language. Whereas the case-based structure sees it as a single set to which you apply operators to get the desired result.

Glad that helped!

jason_m 08-10-2013 10:10 PM

Yes - joining the table on itself will solve this. It can be a handy solution. I've used it several times.

Code:

select
    ml.item,
    ml.value length,
    mw.value width,
    mh.value height
from
    measurements ml
    left join
    measurements mw on ml.item = mw.item and mw.dimension='Wdth'
    left join
    measurements mh on ml.item = mh.item and mh.dimension='Hgth'
where ml.dimension='Len';

When you select from the 'ml' (l as in length) version of measurements, you only want the length rows, so you can filter to those in there where clause. For the 'mw' and 'mh' versions, you can filter down based on the join criteria. Oracle should be able to handle this. Just tested it in sqlite3.

Good luck


All times are GMT -5. The time now is 11:46 PM.