Possible SQL/Query?

[Neo-fite]

I am wondering if the following "query" can be created using PIVOT in SQL. If so/how?

I have the following non-descript Fields which are used...[Year1], [Year2], [Item] and [ItemCost]

What I am looking to accomplish is having Year1 be the RowHeader, with Year2 being a ColumnHeader...however, I would like the ColumnHeader to have 2 "subColumns" which include the Count(Item) and Sum(ItemCost).

For Example
--------- 1992 -------------- 1993 -------------- 1994
------- Item ItemCost ----- Item ItemCost ----- Item ItemCost
1992 |-- 2 -- $20.00 ------- 3 -- $47.00 -------- 3 -- $47.00
1993 |-- 3 -- $35.00 ------- 4 -- $50.00 -------- 8 -- $75.00
1994 |-- 4 -- $50.00 ------- 5 -- $35.00 -------- 9 -- $27.00

Thanks in Advance!

 

[Guus2005]

I can't match your example with what you are trying to accomplish.
You mention 2 "subColumns" which include the Count(Item) and Sum(ItemCost).
I don't see them in your example.

HTH

 

[Neo-fite]

I can't match your example with what you are trying to accomplish.
You mention 2 "subColumns" which include the Count(Item) and Sum(ItemCost).
I don't see them in your example.

HTH

They are just labelled as "Item" and "Item Cost". Also, there is another Field that is used for the query. The query would also require [Status] = "Sold"

 

[Guus2005]

your "query" can't be made in a single query or pivot query. It can be done in a report/form with some VBA code. However your "query" result doesn't make any sense. The rows and columns don't match.