Fields to column

[Aron]

i have a list in a table of lots of products with several price types
like this (example)

SKU PriceType Price
1234 Catalog $10.49
1234 Cost $5.19
1234 Sale $9.19
1234 OffSeason $8.49
1234 Wholesale $6.49
5678 Catalog $26.18
5678 Cost $13.46
5678 Sale $23.06
5678 OffSeason $21.38
5678 Wholesale $16.58
6789 Catalog $6.14
6789 Cost $4.17
6789 Sale $5.65
6789 OffSeason $5.39
6789 Wholesale $4.65

i want to make a query and the results should be like this

SKU Catalog Cost Sale OffSeason Wholesale
1234 $10.49 $5.19 $9.19 $8.49 $6.49
5678 $26.18 $13.46 $23.06 $21.38 $16.58
6789 $6.14 $4.17 $5.65 $5.39 $4.65

how can i do this (im having a lot products (50-60 thousand) in the table i
cant do it manualy

Thanks a million

 

[louisjohnphillips]

i have a list in a table of lots of products with several price types
like this (example)

SKU PriceType Price
1234 Catalog $10.49
1234 Cost $5.19
1234 Sale $9.19
1234 OffSeason $8.49
1234 Wholesale $6.49
5678 Catalog $26.18
5678 Cost $13.46
5678 Sale $23.06
5678 OffSeason $21.38
5678 Wholesale $16.58
6789 Catalog $6.14
6789 Cost $4.17
6789 Sale $5.65
6789 OffSeason $5.39
6789 Wholesale $4.65

i want to make a query and the results should be like this

SKU Catalog Cost Sale OffSeason Wholesale
1234 $10.49 $5.19 $9.19 $8.49 $6.49
5678 $26.18 $13.46 $23.06 $21.38 $16.58
6789 $6.14 $4.17 $5.65 $5.39 $4.65

how can i do this (im having a lot products (50-60 thousand) in the table i
cant do it manualy

Thanks a million

Have you considered:

select A.SKU, A.Catalog, B.Cost, C.Sale, D.OffSeason, E.Wholesale
from tblProductCosts as A,
tblProductCosts as B,
tblProductCosts as C,
tblProductCosts as D,
tblProductCosts as E
WHERE A.SKU = B.SKU and A.SKU = C.SKU and A.SKU = D.SKU and A.SKU =
E.SKU

Note that this will only work if there is one and only one of each
type of price for each SKU.

 

[Rob Parker]

What you need is a crosstab query. They do exactly what you're wanting, and
are very useful. You can set this up easily using the crosstab wizard
(which will also give you an additional column [Total Of Price] by default;
or you can use the QBE design grid and select Crosstab as the query type,
drag each filed into the grid, choose Row Heading for the [SKU] field in the
Crosstab line, Column Heading for the [PriceType] field, and Value for the
[Price] field, with Sum selected for this field in the Totals line. When
you do so, you will finish up with a SQL statement like:

TRANSFORM Sum(tblProducts.Price) AS SumOfPrice
SELECT tblProducts.SKU
FROM tblProducts
GROUP BY tblProducts.SKU
PIVOT tblProducts.PriceType;

If there are multiple entries for the SKU/PriceType combinations, the query
will display the sum of all entries for each pair. You can use First to
select the (arbitrary, unless you've applied a sort) first price if there
are multiples. You can also produce averages, maximum, minimum, etc; the
dropdown in the Totals line shows what is available.

HTH,

Rob