Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
orozcoc
Starting Member
13 Posts |
 Posted - 2002-09-16 : 15:15:34
|
| I need to do a cross-tab tabulation without adding or getting totals. This is how the source data looks like:Mid | TestAlias | Value----- ------------- -------11| test1| 21.6511| test1| 6.3212| test1| 21.6212| test1| 18.2113| test1| 27.0311| test2| 1412| test2| 20and the desired results should be like:Mid | test1 | test2 ----- ------ -----11 | 21.65 | 1412 | 21.62 | 20...etcI already developed a long script but I'm still thinking there should be better and more efficient ways to do this, my script is dynamic and takes care of whatever number of tests appear, but it is way too long and uses 2 cursors which obviously are eating up resources, plus is way too long.so far I've found scripts to do cross-tabbing but all of them are attempting to do an aggregate function which is not my case.Thanks a lot to whomever can help with this. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
|
|
orozcoc
Starting Member
13 Posts |
Posted - 2002-09-16 : 16:42:46
|
| It worked great, thanks a lot. Now I am facing the problem of having to show repetitions, so basically one row can appear twice having two different set of values, any ideas on how to change your SP to handle this ?Thank you very much. |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-09-16 : 16:49:26
|
| Nothing comes to mind. The problem is that if you're not aggregating, then you have to choose one and only one of the values, and the only way to do that is with Min or Max. SQL, as a language, works entirely on the VALUE of the data being stored, not its position or other non-value related attribute. There's no way to do what you want without some kind of summarizing, or using GROUP BY on the value column as well...but then you'll end up with nulls for the TestAlias pivot columns. |
|
|
|
|
|
|
|
|
cross-tab without aggregates