SEO

#1 (**permalink**) 02-28-2008, 06:47 PM

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

#2 (**permalink**) 02-28-2008, 06:48 PM

> 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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

cross tab query