Crosstab Query in SQL Server 2000
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_Paramet开发者_如何学Goer 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
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.
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
精彩评论