Long to Wide

[MFCLARKE]

Hi there.

I have a table which contain 3 columns: ID, Name and Value

The ID is not a PK and can repeat, the name will remain constant for each ID, but the value will vary but always relate back to the specific ID.

I need to get this from the current long table format into a wide table with columns: ID, Name, Value1, Value2, Value3 etc. There will be a maximum of 10 values, but the number of values will vary.

If anyone can shed any light on this one, I would be very appreciative.

Thanks

 

[blom0344]

Possible strategy is to use the
rank() over (partition by ID order by value) strategy to assign a numbering from 1 - 10 for the individual ID values.
(For instance create a view with this addittional column)

Then use 10 case-statements with max operator to spread the ranked values in 10 columns using an SQL over the view.

T. Blom
Information analyst
tbl@shimano-eu.com