cross tab query

[bigmac]

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

[louis nguyen]

> 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!!

Hi Bill,

Put your table into a hashish table, implement the IUnpronounceable
interface to convert it to XML, overload it with a semi-private method
name foo, call it from another method with a meaningless name that is
also a swear word, drag and drop into the trash can (sorry I meant
recycle bin), and do start->run->format c:. Seriously, I think this
might give you an idea.

SELECT
itemID,
name=MAX(case when propertyID='name' then value else null end),
color=MAX(case when propertyID='color' then value else null end),
size=MAX(case when propertyID='size' then value else null end)
FROM Property
GROUP BY itemID