I've got a project where I've been "given"^[1] 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 insert into spam EXEC crazySproc @p1 = 'crazy', @p2 = 'sproc'. And then you're off to the races. Sort of.

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.^[2]

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:

+------+--------+---------+-----------+-----------+--------+
| val1 | val2   | ...valn | colorVal1 | colorVal2 | ...etc |
+------+--------+---------+-----------+-----------+--------+
| 5    | 8.23   | ...     | red       | green     | ...etc |
+------+--------+---------+-----------+-----------+--------+

We want to have...

+------------+--------------+-------------+---------+--------------------------+-----------+
| metricName | metricAmount | metricColor | ordinal | label                    | isPercent |
+------------+--------------+-------------+---------+--------------------------+-----------+
| val1       |            5 |         red |       1 | This is the first value  |         0 |
| val2       |        8.23  |       green |       2 | This is the second value |         1 |
| ...        |              |             |         |                          |           |    
+------------+--------------+-------------+---------+--------------------------+-----------+

We can join on metricName pretty easily to get those extra fields on the right, though, as always, I hate joining on character strings. But desperate times and all that; we're joining to a sproc result that only expected to be used for one purpose, and didn't anticipate reuse.

So how to turn the first table into one that can be used to build the second? UNPIVOT it, turning columns into rows.

Generic case

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 UNPIVOT. Let's just go ahead and look at an extended but simplistic example:

create table #unpivotMe
(
    id int identity(1,1) not null,
    col1 varchar(50),
    col2 varchar(50),
    col3 varchar(50)
);
go

insert into #unpivotMe (col1, col2, col3)
values
    ('1v1', '1v2', '1v3'),
    ('2v1', '2v2', '2v3'),
    ('3v1', '3v2', '3v3');

select * from #unpivotMe

So far, so simple enough...

+----+------+------+------+
| id | col1 | col2 | col3 |
+----+------+------+------+
| 1  |  1v1 |  1v2 |  1v3 |
| 2  |  2v1 |  2v2 |  2v3 |
| 3  |  3v1 |  3v2 |  3v3 |
+----+------+------+------+

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:

1. ignore it,
2. translate it into a single value in a row,
3. ... or display it "as-is", without UNPIVOTing.

Let's start by unpivoting every coln column (option 2 across the board), turning each named column into a row value.

select * from #unpivotMe
unpivot
(
    colValue for col in (col1, col2, col3)
) as unpivoted

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.

+----+----------+------+
| id | colValue | col  |
+----+----------+------+
| 1  |      1v1 | col1 |
| 1  |      1v2 | col2 |
| 1  |      1v3 | col3 |
| 2  |      2v1 | col1 |
| 2  |      2v2 | col2 |
| 2  |      2v3 | col3 |
| 3  |      3v1 | col1 |
| 3  |      3v2 | col2 |
| 3  |      3v3 | col3 |
+----+----------+------+

Just for fun, let's experiment with option 3, "display a column as is", by leaving col3 out of the unpivoting this time, but keeping it in the SELECT clause.

select * from #unpivotMe
unpivot
(
    colValue for col in (col1, col2)
) as unpivoted

Fewer rows, and an extra column.

+----+------+----------+------+
| id | col3 | colValue | col  |
+----+------+----------+------+
|  1 |  1v3 |      1v1 | col1 |
|  1 |  1v3 |      1v2 | col2 |
|  2 |  2v3 |      2v1 | col1 |
|  2 |  2v3 |      2v2 | col2 |
|  3 |  3v3 |      3v1 | col1 |
|  3 |  3v3 |      3v2 | col2 |
+----+------+----------+------+

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.

create table #valuesAndDisplayInfo
(
    val1 decimal(5,2),
    val2 decimal(5,2),
    val3 decimal(5,2),
    colorVal1 varchar(15),
    colorVal2 varchar(15),
    colorVal3 varchar(15)
)

insert into #valuesAndDisplayInfo
    values
    (5, 8.23, 98.85, 'red', 'green', 'yellow');

select * from #valuesAndDisplayInfo;

That gives us the giant single row of data that parallels what the sproc I talked about gives me.

+------+------+-------+-----------+-----------+-----------+
| val1 | val2 | val3  | colorVal1 | colorVal2 | colorVal3 |
+------+------+-------+-----------+-----------+-----------+
| 5.00 | 8.23 | 98.85 |       red |     green |    yellow |
+------+------+-------+-----------+-----------+-----------+

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 val[0-9]*? Yuck.

So let's UNPIVOT.

select * from #valuesAndDisplayInfo
unpivot
(
    value for field in (val1, val2, val3, colorVal1, colorVal2, colorVal3)
) as valuesAsRows

Whoops!

The type of column "colorVal1" conflicts with the type of other columns specified in the UNPIVOT list.

Remember that we have to have all of the column types the same in our UNPIVOT list. Let's get cast crazy.

select field, value from (
    select
        cast(val1 as varchar(15)) as val1,
        cast(val2 as varchar(15)) as val2,
        cast(val3 as varchar(15)) as val3,
        colorVal1,
        colorVal2,
        colorVal3
    from #valuesAndDisplayInfo
) as castedValAndInfo
unpivot
(
    value for field in (val1, val2, val3, colorVal1, colorVal2, colorVal3)
) as valuesAsRows

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.

+-----------+--------+
| field     | value  |
+-----------+--------+
| val1      | 5.00   |
| val2      | 8.23   |
| val3      | 98.85  |
| colorVal1 | red    |
| colorVal2 | green  |
| colorVal3 | yellow |
+-----------+--------+

Here's the bullheaded, inefficient, magic string way around that that we probably oughta integrate earlier.

-- Note that we're selecting into a "real" table now
-- Probably should use a temp table.
select field, value into unpivotedVals from (
    select
    cast(val1 as varchar(15)) as val1,
    cast(val2 as varchar(15)) as val2,
    cast(val3 as varchar(15)) as val3,
    colorVal1,
    colorVal2,
    colorVal3
    from #valuesAndDisplayInfo
) as castedValAndInfo
unpivot
(
    value for field in (val1, val2, val3, colorVal1, colorVal2, colorVal3)
) as valuesAsRows

select v1.field, v1.value, v2.value as color from unpivotedVals v1
    inner join unpivotedVals v2
        on v2.field = 'color' + v1.field

Success. I don't love it, but you can see how unpivoting helped us get here.

+-------+-------+--------+
| field | value | color  |
+-------+-------+--------+
| val1  |  5.00 |    red |
| val2  |  8.23 |  green |
| val3  | 98.85 | yellow |
+-------+-------+--------+

Pretty good introduction and further exploration of UNPIVOT at those links, which is also where I found out about the type and length restrictions for UNPIVOT. I haven't read all the way through, but it looks like he has a method of using APPLY that might make this less painful.

Again, The Right Way is to rewrite the sproc, which I think I'm going to do, but this is a nice an incredibly kludgey, defensive (as in it doesn't affect any of the "given" code) method to get from A to B.

[1] 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?

[2] "... on which we wish to operate"?

Labels: SQL