469,111 Members | 1,961 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,111 developers. It's quick & easy.

dynamically trasnpose rows into columns

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

Dec 10 '05 #1
19 12043
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

Dec 10 '05 #2
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.

Dec 10 '05 #3
(ma**@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, es****@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
Dec 10 '05 #4
--CELKO-- (jc*******@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, es****@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
Dec 10 '05 #5
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

Dec 10 '05 #6
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

Dec 12 '05 #7
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--" <jc*******@earthlink.net> wrote in message
news:11**********************@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.

Dec 12 '05 #8
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.

Dec 14 '05 #9
>> 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.

Dec 14 '05 #10
> 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.


Exactly. I guess we might eventually get another theory, better aligned
with reality

Dec 14 '05 #11
I have a similar issue as the good doctor. Mine is with serial numbers.
I work at a manufacturing facility that makes printed circuit board
assemblies for a wide range of applications and companies (from vacuum
cleaners to automotive to medical). Some of these devices have more than
one unique serial number. One recently has more than one serial number
type with three unique serial number of one type, 1 each of 4 other
types. This makes 5 types of serial numbers and 7 unique serial numbers.

I have a Serials_Number_Generated table that has the following
fields/elements/columns/(whatever you call them):
Serial_Number_ID bigint PK Identity
Serial_Number_Value nvarchar 100
Serial_Number_Index int
Serial_Number_Style_ID bigint
Serial_Transaction_ID bigint

When serial numbers are created and or stored in the database(whatever
you call it), I send the information to a stored procedure to insert
into a table/entity/collection of information about an item or
event/(whatever you call them). Anyway, that I add a transaction to a
Serial_Number_Transactions table, identifying the Operator_ID,
Transaction_DateTime, Facility_ID. I use the Transaction_ID (the PK and
Identity field for the transactions table) in the serial numbers
generated table. This is a one-to-many relationship to ensure that no
invalid transaction IDs are entered.

I have implemented this structure already and if it a bad structure, any
suggestions will be appreciated.

Anyway, the problem I have is this one board with 7 unique serial
numbers on it. The customer would like to have a serial number report
identifying the serial numbers programmed into each board.

I have another table, Serial_Numbers_Utilized to track the serial
numbers that are actually utilized and assign them to a unique Board_ID
(The afformentioned printed circuit board assembly). The
schema/structure/layout(whatever you call it) of this table is:
Serial_Number_Utilized bigint PK (identity)
Serial_Number_ID bigint
Board_ID bigint
Serial_Transaction_ID bigint

When my programming/MDA(Manufacturing Defect Analyzer) station goes to
program the board, it retrieves the unutilized, generated serial numbers
from the database that is referenced in yet another table and adds a new
record to the Serial_Numbers_Utilized table with a link to a new
transaction ID for this action as well as the unique board id the
numbers are being assigned to and the serial id for the serial value
assigned to the board. So for each board that makes it through the
programmer station, there will be 7 new records in the
Serial_Numbers_Utilized table/entity/(whatever you call it).

The customer would like a report for each board identifying the serial
numbers that were utilized to program this board. I started creating a
view/query/select/(whatever you call it) that I could import into Excel
for a report to the customer that had one board per row in the Excel
worksheet.

I've reasoned that I need a few nested Select queries focused for this
particular product (instead of a more general one for all products). I
first created a select statement to get a list of just the boards that
passed all stations (I won't go into the extra detail about the Boards,
Board_Transactions, Boards_Tested,Test_Records, etc tables here). I
filtered for the boards that are of this unique board part number. I
also sorted by board Id. This select statement consists of Inner Joins
of several tables but returns:
Board_ID
Serial_Number_Value
Serial_Number_Style_ID
Serial_Transaction_ID

Each serial will have a uniqe serial transaction id, which is in the
order the serials were added, which is also important for the three
repeating serials.
What I get is
Board_ID, Serial_Number_Value, Serial_Number_Style_ID,
Serial_Transaction_ID
21,A (I'll conceal the actual SN for security),1,70157
21,B,2,70162
21,C,3,70163
21,D,4,70159
21,E,4,70160
21,F,4,70161
21,G,5,70158
43,H,1,70278
43,I,2,70283
etc...
The report would look something like this:
Board_ID,Type1,Type2,Type3,Type4 0, Type4 1, Type 4 2, Type 5
21,A,B,C,D,E,F,G
43,H,I
Of course there are actual serial values inplace of the letters. I was
going to create select statements that used the above result set/record
set/table/data(whatever you call it) as the source and then create a
master select statement that joined all the tables on board id, but I
run into a problem with the Type4, where there are three records per
board, order is important here also.

I found a reference to cross-tabs/pivots/(whatever you call them) that
shows how to use a T-SQL CASE statement in an aggregate function but I
cannot consolidate the numbers in any way here and especially don't want
to risk having SQL convert a trinary number into a bigint and adding it
to another.

I also found a reference to creating a csv record inside the field for
that board record. In this case, you'd have three valuees seperated by 3
columns for each field returned. I may have to go with this method, but
it only seemed to work in Query Analyzer. When attempting to do it in a
view, it timed out. This uses a UDF I found on another site.

Has anyone found a way to do this or will I just need to create a
seperate table identifying the serial_id and ordinal position (1,2,3)
that I'll join for that query. I would have to somehow go back to all
the serial numbers generated with that type joined with the ID's
utilized and this new table and enter 1,2,3,1,2,3,1,2,3 manually or with
another piece of software. I'm thinking this is my only hope at this
point.

Sorry for the long post.

*** Sent via Developersdex http://www.developersdex.com ***
Dec 16 '05 #12
Celko??? Perhaps you might have some insight?

The gentleman has correctly stored his data in relatively, for purposes
of this discussion, normalized form.
Now, he wants to present it in human readable form using standard,
simple, SQL select commands.

Mr. BIhn, you might look into Pivot tables in Excel. Drag your card
thingy record on the left side, and the non-unique serial numbers
across the top right.

Towards the mononamed CELKO (not to be confused with the artist
FORMERLY known as Prince,) Fundamentally, SQL does not solve this
problem, and you will have to resort to kludges in order to solve this
fundamental, yet oh so common business need.
Or, do what the old timers did, and tell the users they really don't
need this information, they just think they do.

Dec 16 '05 #13
There is a way to do a pivot using temp tables and dynamic SQL... I
have used that with some success, i.e. get the distinct values, make
columns for them at run time in a temp table, fill the values and then
get the results. Doesn't SQL 2005 have the ability to do it without the
kludges?

Doug wrote:
Celko??? Perhaps you might have some insight?

The gentleman has correctly stored his data in relatively, for purposes
of this discussion, normalized form.
Now, he wants to present it in human readable form using standard,
simple, SQL select commands.

Mr. BIhn, you might look into Pivot tables in Excel. Drag your card
thingy record on the left side, and the non-unique serial numbers
across the top right.

Towards the mononamed CELKO (not to be confused with the artist
FORMERLY known as Prince,) Fundamentally, SQL does not solve this
problem, and you will have to resort to kludges in order to solve this
fundamental, yet oh so common business need.
Or, do what the old timers did, and tell the users they really don't
need this information, they just think they do.


Dec 16 '05 #14
Thanks for the quick replies. I tried using the Excel pivot table, but
it simply shows the same number of columns as serial number styles and
displays a count of the number of each style for each board ID:

BoardID,Style1,Style2,Style3,Style4,Style5
21,1,1,1,3,1
43,1,1,1,3,1
76,1,1,1,3,1
78,1,1,1,3,1
etc...

Like the MSDN article on cross-tabs, it wants to perform some aggregate
function on the serial number values in the data area.

Thanks again.

*** Sent via Developersdex http://www.developersdex.com ***
Dec 16 '05 #15
ARG!!! you are right. let me think on what we've done in teh past.....

I know you can use a cursor and build a string. ugly though.

Dec 17 '05 #16
The length of the post is fine, but this sounds like a weird way to do
business! The closest thing I can think of is a concatenation (or
"shop tag") code that keeps getting longer as a piece moves from
station to station. I have seen two serial numbers due to a merger and
two inventory systems, but not anything like seven of them!

My first attempt would be to have a table with seven columns that looks
like the report you gave. Adding a board id of your own begs the
question as to what all these other things mean in terms of a data
model.

This might be more than we can do in a Newsgroup.

Dec 22 '05 #17
Thanks for the additional replies.

I realize this has got to be an unusual occurrence. This particular
product is an RF transmitter that has the seven serial numbers
programmed into the microcontroller EEPROM at the programmer station at
the same time. To avoid violating NDA with the customer, suffice it to
say that these are serials for multiple companies products.

The reason for a board ID is not so much for this particular product but
does come in useful if we want to cross reference a particular serial
number and what other serial numbers were assigned to that board. It
serves as a common tie to all the serial numbers assigned to a board. I
had originally concepted simply tracking the singular serial number
through the process, primarily test and packout and RMAs, but we have
some "boards" that actually are more accurately referred to as
"assemblies" with sub-assemblies, each with their own serial numbers.
The original concept I had was to identify a "primary serial number" to
which secondary serial numbers would be assigned. The problem with this
was that when other engineer's go to implement this, or more
importantly, if an operator is prompted to scan the serials at a testing
station, you have to add all sorts of validations to ensure the proper
serial number is scanned. We have so many different variations from a
wide array of customers that I decided it would be easier to go with the
board ID. It also will help to implement some form of tracking for
boards that actually have no serial number assigned to them.

Additionally, the current method of barcoding for one of our customers
has us using multiple pieces of information within the barcode. If it
comes back as an RMA for a software update or kludge (in electronics,
this can involve adding jumper wires, piggy backing FETs, etc), we have
been scanning in the old barcode to retreive the serial number part and
printing out a completely new barcode with the same serial part. The
problem with this method is a new label is printed (cost) and the
operator has to remove the old label, clean the board, and apply the new
one (additional handling = bad). Hopefully, I'll be able to get this
practice stopped sooner than later and allow the tracking and validation
of proper RMA handling to occur by tracking the board in the database by
whatever serial number is scanned. Eventually, I'd like to see the
practice of encoding so much information in the barcode ended and
utilizing the serial number barcode as a unique assembly identifier and
that only. This is a greatly condensed description of what we are trying
to accomplish here, but hopefully sheds some light on the logic of the
seperation between serial number ID and board ID.

As a side, we also do complete box builds of an assembly that has as
many as three circuit boards (each with their own serial number), our
company's unique identifier for the assembly, and as many as three
stinking serials on the outside of the assembly as mandated by our
customer that we are building the assembly for and the end-customer
where we are shipping them.

For this particular product, which is very unique from the others, I am
thinking of adding a table, call it Type_4_Order that has:

PK Type_4_Order_ID bigint Identity
Board_ID
Serial_Number_ID -- From the Serial_Numbers_Generated table
Serial_Number_Position -- 1,2, or 3

I will be able to then create a two column SELECT resultset with
Board_ID, Serial_Number_ID for each of the ordinal positions. With this
resultset, I'll Inner Join with the other nested SELECT statements
returning their individual results.

I know this may look ugly to many of you, but you should see the Access
databases we are using for much of the manufacturing end of our company.
Our IS department is directed towards business applications by
management and we in engineering and maunfacturing are left to our own
devices.

Any additional comments/suggestions are welcome.

Thanks again.


*** Sent via Developersdex http://www.developersdex.com ***
Dec 22 '05 #18
wow. Celko suggesting a totally flattened, junk data model. by far,
this is the worst solution so far. A better solution is to brute force
a temp table and populate it each time you want the report. If you do
it this way, at least your number of columns can be dynamic.

Add an identity field. Trust me, you will like it for hte long term.

On your pivot table, can you use a "min" function as your aggregation?
If so, maybe we can "cheat" the pivot table into diplaying the minimum
color for each identity.

Dec 22 '05 #19
Hi There,

Try this It might help you.
Create View vwtmpData1
As
Select 1 DeviceID ,'color' FieldName,'red ' FieldValue
Union All
Select 1,'shape','square'
Union All
Select 1,'Weight(kg)','2.0'
Union All
Select 2,'shape','round'
Union All
Select 2,'Weight(kg)','1.5'
Union All
Select 3,'color','blue'
Union All
Select 3,'shape','oval'
Union All
Select 3,'Weight(kg)','1.0'
Go

Declare @sql varchar(2000)
Set @sql = 'Select DeviceID '
Select @sql = @sql + ', Max( Case When FieldName = '''+FieldName +'''
Then FieldValue End) As [' + FieldName +']' + char(13) + char(10)
From vwTmpData1 Group By FieldName

Set @sql = @sql + ' From vwTmpData1 Group By DeviceId'
Print @sql
Exec(@sql)

All said and done !! Still knowing the number of columns is good
insight in the bussiness needs of the client . What you are trying to
acheive is EAV (Entity Attribute Value), which is not so good. Just
think how much time it will waste to write a complex query.

With Warm regards
Jatinder Singh

Dec 23 '05 #20

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Craig G | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.