0

I'm trying to transpose row to columns sequentially with SQL in MS ACCESS database . I have the SQL below, but it's not right yet: I want in one query. If there is an answer then I don't want to use the function in MS Access because I want to use the SQL for VB.Net. Please Guide Me.

Thanks

  TRANSFORM Sum(Tableproduct.[Qty]) AS SumOfQty
SELECT Tableproduct.Codeproduct AS CodeProduct, Tableproduct.Colour AS Colour, Sum(Tableproduct.Qty) AS Total
FROM Tableproduct INNER JOIN SizeProduct ON Tableproduct.Size = SizeProduct.Size
WHERE Tableproduct.Codeproduct = 'A'
GROUP BY Tableproduct.Codeproduct, Tableproduct.Colour
PIVOT SizeProduct.Size;

Results from the above SQL code :

CodeProduct Colour Total L M S XL
A Black 30 20 10
A White 25 10 15

I want to add the SQL code below into one query

ORDER BY SizeProduct.Sequence ASC

Sample Data :

Table TableProduct

CodeProduct Colour Size Qty
A Black XL 10
A White S 15
A Black M 20
A White L 10

Table Sizeproduct

Sizeproduct Sequence
S 1
M 2
L 3
XL 4

Desired Result

CodeProduct Colour S M L XL TOTAL
A Black 20 10 30
A White 15 10 25

Column Total position

Improve this question

1 Answer 1

Reset to default
2

Specify order of pivot fields with IN() function:

TRANSFORM 
    Max(Qty)
SELECT 
    CodeProduct,
    Colour,
    Sum(Qty) As Total
FROM 
    Tableproduct
GROUP BY 
    CodeProduct,
    Colour
PIVOT 
    [Size] In ('S', 'M', 'L', 'XL')

Use yet another query to force Total field to end:

Select
    CodeProduct,
    Colour,
    S, M, L, XL,
    Total
From 
    Query1
Improve this answer
Sign up to request clarification or add additional context in comments.

16 Comments

Sorry I was late in your response, thank you for your answer. This means I have to manually update the list of sizes in the SQL code. Can I directly refer to the table Sizeproduct because there are approximately 30 sizes and what do I do if I want to add a total column as desired result?
Then you must edit the In clause before running the query. Or you may be able to implement another technique as described in paragraph The special case: Multi-Value fields in my article Join (concat) values from one field from a table or query.
Can I use an inner join to the table Sizeproduct ? and how can I add a total column in the sql code?
See edited answer for the Total. Not sure what an inner join should do, though.
You can't. The order is alphabetic or determined by the IN sequence, and this cannot be set dynamically. So, once again, rewrite the SQL of the query before running it.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.