[thelist] query help (ms sql 2000)

Matt Warden mwarden at gmail.com
Fri Jan 12 08:17:19 CST 2007


On 1/12/07, Brian Cummiskey <brian at hondaswap.com> wrote:
> itemno, qty1, qty2, qty3, qty4, qty5, qty6, qty7, qty8, qty9, pri1,
> pri2, pri3, pri4, pri5, pri6, pri7, pri8, pri9
>
> in the above example, the data would look like this:
>
> item123, 1, 2, 3, 9999999, 0, 0, 0, 0, 0, 1999, 1599, 1599, 1499, 0, 0,
> 0,0, 0
...
> I am at a loss for trying to come up with a solution that groups these
> prices.  Ideally, I would like a pure sql solution without resorting to
> scripting a loop as it is currently.
>
> I'm thinking temporary table formatted in a way that makes it useful for
> grouping, but I'm still not sure which way to attack this.

Untested:

select itemno, qty, price
from (
	select itemno, qty1 as qty, pri1 as price
	from table
	union
	select itemno, qty2 as qty, pri2 as price
	from table
	union
	select itemno, qty3 as qty, pri3 as price
	from table
	union
	select itemno, qty4 as qty, pri4 as price
	from table
	union
	select itemno, qty5 as qty, pri5 as price
	from table
	union
	select itemno, qty6 as qty, pri6 as price
	from table
	union
	select itemno, qty7 as qty, pri7 as price
	from table
	union
	select itemno, qty8 as qty, pri8 as price
	from table
	union
	select itemno, qty9 as qty, pri9 as price
	from table
)
order by itemno, qty

You can drop the outer shell of the query if you don't care about
ordering, or if you were really asking how to get this information for
a specified itemno (couldn't tell from your description). The above
gets all itemno's.

-- 
Matt Warden
Cleveland, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.



More information about the thelist mailing list