Flipping rows with columns in SQL
-
I have just spent 2 solid weeks coding an analysis feature which makes heavy use of dynamic SQL. Basically, I now have a lovely and fully functional stored proc which will give me data which is cross referenced by the question specified, as well as a host of other parameter options (view as f or %, a subset clause, options to exclude blank answers and even adding comparitive data from a different survey).
My data comes out fine, in the following order:
AGREE DISAGREE NO OPINION Not Answered 2002 Survey - Q2 A 37.84% 14.41% 0.9% 0% 2002 Survey - Q2 B 12.61% 3.6% 0.9% 0% 2002 Survey - Q2 C 0.9% 5.41% 2.7% 0% 2002 Survey - Q2 D 5.41% 2.7% 0% 0% 2002 Survey - Q2 Overall 63.06% 32.43% 4.5% 0% A 41.75% 12.62% 0.97% 0% B 12.62% 5.83% 0.97% 0% C 1.94% 8.74% 0.97% 0% D 13.59% 0% 0% 0% Overall 69.9% 27.18% 2.91% 0% I'm now wanting to redraw the data with the agree/disagree etc as the vertical axis...and I don't want to touch the dynamic stored proc code again...I've been lost in that for too long now!
Is there any way to do this entirely in SQL?
...oooOO*OOooo... -
Before it get's much more complicated, perhaps you should look into OLAP Services which comes free with MS-SQL... OLAP (online analytical processing) is on of the "other" things on your installation disk that many people over look. If you need lot's of dynamic SQL and need to be able to do things in a "crosstab" or like in "pivot tables", OLAP may be an answer.
Admittedly, OLAP is a bit of a pain in some aspects so.... You could use the output of your stored procedure by first having it populate a table variable or a working table (a permanent table which is usually truncated when expired data is repopulated). If you really, really don't even want to open the sp for edit 'cause you don't want to take the chance, you could make a copy of it and play with the copy or use the output of the sp using "OpenRowSet".
"OpenRowSet" will use the output of a stored procedure as if it were a table... then you could use one of the many crosstab methods from this forum to "swing things around".
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
I agree with Grasshoppers post. I think you're creating work for yourself here. However, If you just have standard edition maybe that's why you needed todo the work by hand. Alot of this kind of selecting which way you want to display rows and columns is handle under analysis server. Although I do see anything in you comments that suggest you couldn't use it for your problem.
Peter Evans (__PETER Peter_)
-
See if this helps - http://sqlteam.com/item.asp?ItemID=2955
A cube would be a piece of cake to create for this and you have full flexibility with X and Y axis.
------------
Ray Higdon MCSE, MCDBA, CCNA -
Thanks for all the replies guys. Sorry for not replying sooner, but I've just started a new job
As it turned out I was outputting the results straight into a chart object "chartfx". (http://www.softwarefx.com)
On closer inspection there was a property called TRANSPOSE, which did exactly what was needed without even changing the SQL code - just what was needed.
Many Thanks...
James
...oooOO*OOooo...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply