views:

53

answers:

3

I am hoping that someone has attempted this before and I can get some advice before I go any further.

I am looking to produce something similar to a crosstab query in sql-server 2000.

I have a table structure similar to the following:

Item       Item_Parameter      Parameter
id         item_id             id
desc       parameter_id        desc
           value

What I am looking to do is to flatten out the data through a query/stored procedure to make building reports easier.

The ideal solution would produce results such as:

             Parameter.desc[0]      Parameter.desc[1]      Parameter.desc[3]...
item.id[0]   Item_Parameter.value   Item_Parameter.value   Item_Parameter.value
item.id[1]   Item_Parameter.value   Item_Parameter.value   Item_Parameter.value   
+3  A: 

If you're sure there's at most one value for each parameter-item combination, you can use a simple group by:

select  item_id
,       max(case when parameter_id = 1 then value) Par1
,       max(case when parameter_id = 2 then value) Par2
,       max(case when parameter_id = 3 then value) Par3
from    item_paramenter
group by
        item_id

You can use min or avg instead of max: it shoulnd't matter because there's only one value for each parameter per item_id,

Without dynamic SQL, there is no way to return column names based on the description in the parameter table.

Andomar
For how often a parameter is added, this may be the best solution. Any suggestions on a dynamic solution?
Mervyn
@Mervyn: DanP posted a link to a good dynamic solution
Andomar
I came across the following stored procedure that I'm currently modifying. http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables It seems to do what I'm looking for so far. I can see that I'm going to run into issues with maximum varchar length though.
Mervyn
+2  A: 

If you don't mind using dynamic SQL; an elegant solution can be found here.

DanP
A: 

I ended up using a stored procedure (http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables) to create a sql statement dynamically.

Thanks Dan and Andomar

Mervyn