Posted by matt on 12/10/05 05:26

I've seen several posts that begin to address this problem, but have
not found a simple, elegant solution that will accomplish this goal.
The important part of this solution is that it must be completely
dynamic - I have over 40 different categories of devices, each with
different fields, and each search will return only one category. I
have no knowledge of the number or datatype of these field names
beforehand and must use the sp to dynamically create the table and then
transpose the data.

Here is an example

I have normalized data in this format (this is a simplification)

deviceId fieldName fieldValue
1 color red
1 shape square
1 weight(kg) 2.0
2 shape round
2 weight(kg) 1.5
3 color blue
3 shape oval
3 weight(kg) 1.0

I would like to convert this to the format: (note that it must handle
nulls - deviceId 2)
deviceId color shape weight(kg)
1 red square 2.0
2 round 1.5
3 blue oval 1.0

Anyone with any thoughts on how best to accomplish this?

thanks,

Matt

• Next in forum: Re: dynamically trasnpose rows into columns
• Prev in forum: Re: Database security (crosspost)
• Thread view: dynamically trasnpose rows into columns

[Reply to this message]