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
What I want is output:
Item Length Width Height
A1 3 4 5
A2 2 7 1
A3 6 6 6
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
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.
Unless I am missing something...
... I was missing something - see next post...
This should work for any SQL99 RDBMs I think
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?
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!
Yes - joining the table on itself will solve this. It can be a handy solution. I've used it several times.
|All times are GMT -5. The time now is 10:24 PM.|