Put the knife down and take a green herb, dude.
One feller's views on the state of everyday computer science & its application (and now, OTHER STUFF) who isn't rich enough to shell out for www.myfreakinfirst-andlast-name.com
Using 89% of the same design the blog had in 2001.
FOR ENTERTAINMENT PURPOSES ONLY!!!
Back-up your data and, when you bike, always wear white.
MarkUpDown is the best Markdown editor for professionals on Windows 10.
It includes two-pane live preview, in-app uploads to imgur for image hosting, and MultiMarkdown table support.
Features you won't find anywhere else include...
You've wasted more than $15 of your time looking for a great Markdown editor.
Stop looking. MarkUpDown is the app you're looking for.
|Wednesday, June 17, 2015|
I've got a project where I've been "given" code that has lots of display logic in sprocs on SQL Server, and that display logic (colors, in this case) is fairly inextricably tied in with the data I want to use too. That is, we have several tables that hold raw data, and these sprocs both tease out the data and put it, somewhat inextensibly, into what boils down to one giant "row" of data. In other words, the data in the sproc's output is not normalized.
That info is, in the original system, passed on to SSRS (SQL Server Reporting Services, a, afaict, sort of SQL Server specific Crystal Reports equivalency) and translated, somewhat painfully, into SSRS's pseudo-html.
You can get those results into another table pretty easily, via building a table whose structure matches the output and using something like
Because of the way we've got this set up, all the values are in a single row that extends until the end of time, with specialized display column value after specialized display column value interspersed with the raw data we want to operate on.
The Right Thing To Do would be to rewrite the sproc to give us normalized data. But The Quick Thing would be to try and get each column name and value along with that value's display info into a single row we can easily JSON up and send, packaged with a little more display info that'll replace the static setup we have in SSRS, to the client.
So from this:
We want to have...
We can join on
So how to turn the first table into one that can be used to build the second?
I'm not sure why, but I had to stare at some examples for a while before I kinda got what was going on with
So far, so simple enough...
Here's how you need (or how I needed) to think of what comes next -- Each column is a name and value pair. And we have a choice with each column. We can:
Let's start by unpivoting every
The result is reasonably neat. Remember that, in my use case, there are no ids and only one row, so it'd stop after the third row displayed below.
Just for fun, let's experiment with option 3, "display a column as is", by leaving
Fewer rows, and an extra column.
This shows us that what's explicitly listed in the unpivot clause are the only columns that we're going to "explode" into row values, which allows some interesting uses.
There are some crazy caveats, however, like that the cols in the UNPIVOT have to be the same type and length apparently. Check out this link at "If we attempt to use the UNPIVOT operator to do this, we run into trouble". That's pretty painful, and requires some wacky casting to keep up the shortcut charade, below.
One row mash-up use case (kludges ahoy!)
Let's also create something closer to my original use case, so you can tell exactly how it's useful.
select * from #valuesAndDisplayInfo;
That gives us the giant single row of data that parallels what the sproc I talked about gives me.
That's kind of nasty. There are three values with three colors for display, all in the same place. Am I supposed to just JSON that up and look for every label in the format
Remember that we have to have all of the column types the same in our UNPIVOT list. Let's get cast crazy.
Though the casting stinks, that's not puke out loud horrible, but I really wanted the colors to be on the same row as the raw values. Right now, they aren't.
Here's the bullheaded, inefficient, magic string way around that that we probably oughta integrate earlier.
Success. I don't love it, but you can see how unpivoting helped us get here.
Pretty good introduction and further exploration of
Again, The Right Way is to rewrite the sproc, which I think I'm going to do, but this is
 Hrm, not exactly "given". Inherited? What's it called when you have contractors who are hired before you start and pick a stack that's probably not what you would've picked?
 "... on which we wish to operate"?
posted by ruffin at 6/17/2015 04:12:00 PM
All posts can be accessed here:
Just the last year o' posts: