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

assuming the combination (deviceId, fieldName) is unique,
select distinct deviceid,
(select fieldValue from aaa a1 where a1.deviceid=aaa.deviceid and
a1.fieldName='color') color,
....
from aaa

note that your original table is NOT, repeat NOT normalized. You might
need to learn more about normalization

Perhaps the problem is that you are trying to destroy the very
foundation of RDBMS by mixing data and metadata in the schema?

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. You use the wrong words BECAUSE
you have the wrong mental model.

Read a book that will teach you about normalization, RDBMS, and data
modeling. You are not going to get this in a Newsgroup -- you need a
year or more of education.

(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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

--CELKO-- (jcelko212@earthlink.net) writes:
> Perhaps the problem is that you are trying to destroy the very
> foundation of RDBMS by mixing data and metadata in the schema?
>
> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files. You use the wrong words BECAUSE
> you have the wrong mental model.
>
> Read a book that will teach you about normalization, RDBMS, and data
> modeling. You are not going to get this in a Newsgroup -- you need a
> year or more of education.

So when you come to a site, and the user ask you for a report like this,
do you tell them that they have the wrong mental model, and they should
learn about RDMBS? How long does it then take before that customer cancels
the contract with you.

There is a lot of things out there that users need, that does not align
with the theory of the relational models, and I can tell you these users
does give a single damn about relational theory. They want data and reports
so that they make business, and how this data is produced is immaterial
to them. It is our task as IT craftsmen to produce that data (because these
are typically the guys that pays us, so we can ger bread on the table).

There is absolute no excuse for insulting people, just because they are
looking into solve business problems. Go and dig yourself under a rock.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Erland,

Thank you for your supporting statement. It is obvious from my post
that I do not have any formal training in IT, however, I do have a very
real problem that can be improved with a clever IT solution. My formal
training is as a surgeon and I am trying to catalog medical devices to
allow other surgeons to best choose the right tool for the job. And, I
assure you that when patients come into my office, I DO NOT tell them
that they have a complete misunderstanding of anatomy and disease and
that they should take a year's worth of classes before they consult me
again.

I am trying to solve a problem, not impress people with the purity of
my code.

Perhaps you should be less concerned with adhering to rules someone
else taught you and more interested in creative solutions that solve
real world problems.

Erland, thank you for the link. It's a good place to start.

Thank you,

Matthew Weiner, MD
Department of Surgery
Univ. of Maryland Medical Center

hmmmm,

Dr Weiner, perhaps there is an opportunity for further help however.
As an example, if someone comes to you asking for heart medication, you
probably don't just give them the prescription. You ask them what are
the symptoms, and dig into WHY they feel they need it.
Sometimes the patient is right, and the prescription is given. But
sometimes you have better advice for the patient, and can give them a
better solution to their problem then they had dreamed of.

I agree that approach is everything. What specifically are you trying
to do? Perhaps using an expert in the field more closely could lead to
a better solution to the problem you are trying to solve?
doug

Yet again you embarras yourself, do you think being rude sells more books?
You have the unfortunete but well deserved label of 'prat'.

Your inability to understand why the poster wants this exposes your lack of
real industry development experience and exposure.

Reading books is not enough to gain experience, i would suggest you go and
work for a company (assuming anybody will hire you with the attitude you
have) and get some real business experience. I seem to recall you worked for
a failed .com, was the reason for failure anything to do with your attitude?
Companies are often destroyed by their own staff having self agendas,
opinions and don't focus on the goal of the business, rather they focus on
their own selfish goals.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials


"--CELKO--" <jcelko212@earthlink.net> wrote in message
news:1134194687.372370.245330@g44g2000cwa.googlegr oups.com...
> Perhaps the problem is that you are trying to destroy the very
> foundation of RDBMS by mixing data and metadata in the schema?
>
> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files. You use the wrong words BECAUSE
> you have the wrong mental model.
>
> Read a book that will teach you about normalization, RDBMS, and data
> modeling. You are not going to get this in a Newsgroup -- you need a
> year or more of education.
>

from the peanut gallery, the issue of switching rows to columns, and
back is NOT a trivial one. This issue has come up time and again, and
is indeed one of those quasi religious issues that goes towards one of
the limitations of true relational databases.

Some of us try to get around this limitation by re-examining the
problem in hopes of finding a different approach from the business
perspective.

There are other ways of "working around" this limitation.

>> I DO NOT tell them that they have a complete misunderstanding of anatomy and disease and
that they should take a year's worth of classes before they consult me
again. <<

If they come to you and tell that they have applied some leeches and
taken arsenic to cure what they think is cancer, but now need some
help with the incantations, what do you do? You can tell them that
what they are doing is just fine and give them an incantation. We call
that a Kludge in IT. Or tell them that what they are doing is going
to do harm in the long run? Or do you try to solve the real problems?


>> Perhaps you should be less concerned with adhering to rules someone else taught you and more interested in creative solutions that solve real world problems. <<

Those rules are based on math and logic; I got to see the formal
proofs. Your trade is more statistical than mathematical. If IT is
done right, it is a hard science and not "cowboy coding" and kludges.


When you do a surgery, do you always try to find a "creative solution"
without any research to see if your approach is in the literature and
is known to fail? No. You look for proven solutions and methods for
problems that have been solved before. You follow procedures. You
fill out HIPAA documents to prove that you did not do any "cowboy
surgery" on the job.

>> I am trying to catalog medical devices to allow other surgeons to best choose the right tool for the job. <<

Matching tools to jobs is what is called "configuration managment" in
theIT literature. You can buy packaged programs for it which will go
thru a series of questions to produce a weighted list of options.

Ever use Mycin or other computerized diagnostic tools? They were
performing better than 80% of the humans in the 1980's, but I do not
know what level they are at now (I woudl assume some improvements now).
You might be able to use one of them with your data instead of
re-inventing the wheel. That would a terrific advantage -- someone
else will maintain the software and distribute it, hospitals will
already have people who know how to use the package, etc.