Multiple Rows In One Row Query 2

[boggg1]

The database I query is of the form (MUCH simplified)...

Table A:
Product Stage Attribute Value
Alpha One Height V1
Alpha One Width V2
Alpha One Length V3
Alpha Two Height V4
Alpha Two Width V5
Alpha Two Length V6
Alpha Three Height V7
Alpha Three Width V8
Alpha Three Length V9

Table B:
Product Stage Quantity
Alpha One Q1
Alpha Two Q2
Alpha Three Q3

I want to get all product Alpha's data at each stage in one row...
Stage Quantity Height Width Length
One Q1 V1 V2 V3
Two Q2 V4 V5 V6
Three Q3 V7 V8 V9

It must be possible. Thanks for trying.

Boggg1

 

[carp]

I think this should work (although I haven't tested it):

SELECT b.stage, b.quantity, a1.value, a2.value, a3.value
FROM tableB b,
tableA a1,
tableA a2,
tableA a3
WHERE a1.product = b.product
AND a1.stage = b.stage
AND a2.product = b.product
AND a2.stage = b.stage
AND a3.product = b.product
AND a3.stage = b.stage
AND a1.attribute = 'HEIGHT'
AND a2.attribute = 'LENGTH'
AND a3.attribute = 'WIDTH'
ORDER BY b.stage;

 

[BillDHS]

I would suggest a left outer join to prevent dropping products if any value is missing.

Select A.product, A.stage, B.Quantity,
MAX(Case B.height When ISNULL then 0 else B.height end),
MAX(Case B.width When ISNULL then 0 else B.width end),
MAX(Case B.length When ISNULL then 0 else B.length end)

From Table A Left Outer Join Table B

On A.Product = B.Product
And A.stage = B.Stage

Group by 1,2,3

 

[boggg1]

Feedback:

carp, I tried your solution months ago but it did not work. Your mail prompted me to retry in the light of the data problems IT group has been fixing for me. Now it works. You have no idea how hard I have tried to do this and to discover I was right is faintly satisfying.

BillDHS, I haven't tried your solution yet, mainly because I have always used SQL in the form carp writes and I can't yet get my head around writing it the other way (OUTER JOINS and the like). One day...

Can I add BillDHS's outer joins to carp's solution ?
eg AND a1.attribute (+) = 'HEIGHT'

Many thanks from a self-taught SQL'er, both of you.

Boggg1