Posted by Erland Sommarskog on 12/10/05 12:33

(matt@endosearch.net) writes:
> 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?

Check out http://www.rac4sql.net, that's a third-party software that
has good support for this kind of transforms. I have never used it
myself, but people appear to be satisfied with it.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]