LinuxQuestions.org
Support LQ: Use code LQ3 and save $3 on Domain Registration
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 08-05-2013, 09:18 PM   #1
bop-a-nator
LQ Newbie
 
Registered: Sep 2012
Location: North East USA
Distribution: at work: Red Hat Enterprise Linux Server release 5.8 (Tikanga); at home: what do you recommend?
Posts: 24

Rep: Reputation: Disabled
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
 
Old 08-05-2013, 10:28 PM   #2
grail
Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 7,517

Rep: Reputation: 1896Reputation: 1896Reputation: 1896Reputation: 1896Reputation: 1896Reputation: 1896Reputation: 1896Reputation: 1896Reputation: 1896Reputation: 1896Reputation: 1896
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.
 
Old 08-05-2013, 11:00 PM   #3
astrogeek
Senior Member
 
Registered: Oct 2008
Distribution: Slackware: 12.1, 13.1, 14.1, 64-14.1, -current, FreeBSD-10
Posts: 1,872

Rep: Reputation: 642Reputation: 642Reputation: 642Reputation: 642Reputation: 642Reputation: 642
Unless I am missing something...

[SNIP]

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

Last edited by astrogeek; 08-06-2013 at 01:15 AM.
 
Old 08-06-2013, 12:06 AM   #4
astrogeek
Senior Member
 
Registered: Oct 2008
Distribution: Slackware: 12.1, 13.1, 14.1, 64-14.1, -current, FreeBSD-10
Posts: 1,872

Rep: Reputation: 642Reputation: 642Reputation: 642Reputation: 642Reputation: 642Reputation: 642
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
 
1 members found this post helpful.
Old 08-06-2013, 09:42 AM   #5
bop-a-nator
LQ Newbie
 
Registered: Sep 2012
Location: North East USA
Distribution: at work: Red Hat Enterprise Linux Server release 5.8 (Tikanga); at home: what do you recommend?
Posts: 24

Original Poster
Rep: Reputation: Disabled
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?
 
Old 08-06-2013, 02:00 PM   #6
astrogeek
Senior Member
 
Registered: Oct 2008
Distribution: Slackware: 12.1, 13.1, 14.1, 64-14.1, -current, FreeBSD-10
Posts: 1,872

Rep: Reputation: 642Reputation: 642Reputation: 642Reputation: 642Reputation: 642Reputation: 642
Quote:
Originally Posted by bop-a-nator View Post
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!

Last edited by astrogeek; 08-06-2013 at 02:01 PM.
 
Old 08-10-2013, 09:10 PM   #7
jason_m
Member
 
Registered: Jun 2009
Posts: 33

Rep: Reputation: 12
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
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sql select replace wackysiya Linux - Newbie 1 05-07-2013 06:10 PM
SQL select/NULL question action_owl Programming 3 12-23-2010 08:24 PM
SQL SELECT Group by 2 conditions MicahCarrick Programming 1 01-30-2007 10:02 PM
Sql + Oracle Select If ???? smaida Programming 11 12-15-2004 08:13 PM
Bizarre SQL select query acid_kewpie Programming 6 01-20-2004 12:47 PM


All times are GMT -5. The time now is 11:52 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration