Hi - I have what I think is a "simple problem".

We currently have a database table that stores ItemProperties by
ItemID, PropertyID and Value. (The PropertyID references another table
for property names and types.)

This ItemProperties table is indexed and provides a flexible way of
storing our item metadata. However, I would now like to return
recordsets to the application layer that list these properties in
column fashion, grouped by ItemID

I have seen that most cross-tab queries examples assume numerical data
and are based around using SUM and the GROUP BY phrase. But our data
is a mixture of string and numbers (of various formats) and so GROUP
BY is not an obvious solution. I have tried using CASE in the select
list but this returns one row for each property with one column having
the correct value, and all the other colums are NULL. I cannot think
of how to combine these into one full record!!

I could achieve the desired resultset by using a SELECT sub-statement
for every column, but I was hoping there was a more efficient method.

Can anyone offer advice on this? It would be most appreciated.

Best,

Bill