converting rows to columns

[jackjordan]

Hi

I have a table which holds the attributes for particular products

prodid prodname feature featurevalue
1 box 1 length 20
1 box 1 width 10
1 box 1 depth 5
1 box 2 length 25
1 box 2 width 20
1 box 2 depth 5


i want to see my results like this so just wondering how do i create the query

prodid prodname length width depth
1 box1 20 10 5
2 box2 25 20 5

i know i can use a case statement

SELECT
prodid,
max(CASE WHEN feature= 'length' THEN featurevalue
END) AS 'length',
max(CASE WHEN feature= 'width' THEN featurevalue
END) AS 'width'
max(CASE WHEN feature= 'depth' THEN featurevalue
END) AS 'depth'
FROM
products
GROUP BY prodid

but my problem is that 1 do not neccessarily know what the features are as these could change between products so i need to make it more dynamic and not hardcode in the feature names..i want it to be able to create columns for all features that might be in my table

any help appreciated

[ck9663]

See if this helps.

Good Luck!!!

~~ CK

[gpl]

If you are using MSSQL 2000, then this site has the answer
www.sqlteam.com