Convert rows to columns

Dear All,

I want a list of selected orders and the orderproperties as columns
but the properties are stored as row in table ORDER_PROPERTY (only 1
row per order !)

Table: ORDER: ORD_ID, ORD_CLIENTCODE, etc
Table: PROERTY: PRP_ID, PRP_NAME, etc > don't think I need this table
for my view or SP.
Table: ORDER_PROPERTY: OP_ORD_ID, OP_PRP_ID, OP_VALUE

I want a resultset like this ( property-values from 211 till 220 and
if not related show 0 or null)

ORD_ID ORD_CLIENTCODE OP_VALUE ( propertyID=211)
OP_VALUE ( propertyID=212)
1 ab235468
11111 2222
2 ab124578
0 1234
3 ab123654
12365 0
4 ab321456
0 0
5 ab365489
45645 22

Thanks,

Fluppe

Re: Convert rows to columns

Try this :

SELECT O.ord_id,
MAX(CASE WHEN P.op_prp_id = 211 THEN P.op_value END) AS
op_value_211,
MAX(CASE WHEN P.op_prp_id = 212 THEN P.op_value END) AS
op_value_212,
MAX(CASE WHEN P.op_prp_id = 213 THEN P.op_value END) AS
op_value_213,
MAX(CASE WHEN P.op_prp_id = 214 THEN P.op_value END) AS
op_value_214,
MAX(CASE WHEN P.op_prp_id = 215 THEN P.op_value END) AS
op_value_215,
MAX(CASE WHEN P.op_prp_id = 216 THEN P.op_value END) AS
op_value_216,
MAX(CASE WHEN P.op_prp_id = 217 THEN P.op_value END) AS
op_value_217,
MAX(CASE WHEN P.op_prp_id = 218 THEN P.op_value END) AS
op_value_218,
MAX(CASE WHEN P.op_prp_id = 219 THEN P.op_value END) AS
op_value_219,
MAX(CASE WHEN P.op_prp_id = 220 THEN P.op_value END) AS
op_value_220
FROM Order As O
JOIN Order_Property AS P
ON O.ord_id = P.op_ord_id
GROUP BY O.ord_id

HTH,

Plamen Ratchev
http://www.SQLStudio.com