This is a follow up to the question at the link below.
I have my schema set up as stated in the above solution using the hybrid approach - one central table for the shared attributes and then EAV tables to describe the non-shared attributes. In some cases, for certain forms and reports, I need to be able to display data as column headings that are displayed as row headings in this schema. Instead of having the following information displayed for one property for example:
Type : Residential
Owner : John Smith
Year Built : 2005
Lot Size : 1 acre
Sewer : Septic
Water : Well
I need to be able to display this data summarized for multiple properties like this :
Type : Owner : Year Built : Lot size : Sewer : Water
Property 1 Residential John Smith 2005 1 acre Septic Well
Property 2 Commercial Bob Thomas 1987 5 acres Public Public
Property 3 Residential Mark Jones 2011 .38 acre Public Well
I thought this was what a crosstab query was for but I can't get it to display the actual data elements. So far it will only let me count, or sum the elements. I want to actually display them.