I have the following table showing the number of times 2 different products are related, and the match %.

What I want (or to be more accurate, what has been asked of me) is for each Product A to be listed only one time and for the Product B Data to be listed horizontally. So for Product A 10854, I want to add columns listing Prod B, Prod B Desc, Times Related, and Related %, for each of the Prod B items (5028, 4777, 9176). Total of 12 columns added.

Is there a way to do this?


Click to view attachment

look at crosstab queries

That might work for the data sample I posted but the actual query is much larger (over 1000 rows) and has alot of 1:1 matches with Prod A & B. Doing a cross tab would create a much wider table than I am wanting.

Would it be possible to just create a crosstab with generic column headers like Row 1, Row 2, Row 3 rather than (Prod B items) 5028, 4777, 9176?

I think Crosstabs might work but I need to identify Row numbers. For example I would like my query to specify Row 1 for the first related product, Row 2 for the second, etc. And the Row count would increment until there was a change in A Product.

(see screenshot)

Thanks!

Click to view attachment