September 13, 2011 at 2:11 pm
Hello - I work in a school district and the medical dept. keeps track of students ailments. A student may have several ailment codes and comments. I need to pivot the list, so that instead of having multiple rows of the same student with a new row for each med code and comment pair, I need to have a single row for each student with the code/comment pairs as additional columns
so instead of: (multiple rows per student's ailment)
ID MED_Code MED_Comment
100 ALE Allergic to peanuts
100 SRG Recent surgery on arm
100 ORT Knee problems
105 ALE Allergic to shrimp
105 MDO Acetaminophen 500mg
i need this: (one row per student - multiple fields for each code/comment pair)
ID MED_Code0 MED_Comment0 MED_Code1 MED_Comment1 MED_Code2 MED_Comment2
100 ALE Allergic to peanuts SRG Recent surgery on arm ORT Knee Problems
105 ALE Allergic to shrimp MDO Acetaminophen 500mg
nothing needs to be counted or aggregated. The challenge is how to pivot the two code/comment fields together
Thanks for any suggestions.
David Walker
September 13, 2011 at 2:25 pm
Please have a look at the CrossTab article referenced in my signature. This concept will work if you have a known number of comments. Otherwise you might want to read the second related article (DynamicCrossTab).
Or, to do it the easiest way: let the application layer transpose the data... 😉
September 13, 2011 at 2:59 pm
Thank you, Lutz for your quick reply. I'm reading the two articles on cross-tabs and they look promising. I'm not sure what you mean by having the application layer do this. I am currently solving this puzzle by moving the code/comment pairs, one set at a time, in Excel. It just seems like a lame solution. Especially with the power of SQL 🙂
September 14, 2011 at 1:11 am
David Walker-409102 (9/13/2011)
Thank you, Lutz for your quick reply. I'm reading the two articles on cross-tabs and they look promising. I'm not sure what you mean by having the application layer do this. I am currently solving this puzzle by moving the code/comment pairs, one set at a time, in Excel. It just seems like a lame solution. Especially with the power of SQL 🙂
You're right, EXCEL is not really an application layer... 😉
But even this "electronic spreadsheet" has several options to do the task automatically, even without using any VB code. Just plain functions.
The reason you'll see the advice I gave rather frequent and from several people, is (at least in my case) based on the fact that the power available for the db system is too valuable to be "wasted" on presenting data in a denormalized form. But if there's enough free resources on your system and the data volume is rather small, then it may make sense to utilize the resources. Always better than storing the data in the wrong format in the first place.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply