sql: - replace and unpivot functions in a query

Fiddle Example here: http://sqlfiddle.com/#!3/419ec/3

SQL server 2008.

I was wondering whether someone can please explain to me what is going on in the select query below with the replace function and unpivot function. I'm a newbie to sql and I don't understand the logic of that type of query (de-normalizing a table).

CREATE TABLE TableB
    ([date] datetime, [Id] int, [name] varchar(3), [blah1] varchar(4), [hour1] int, [hour2] int, [hour3] int, [hour4] int)
;

INSERT INTO TableB
    ([date], [Id], [name], [blah1], [hour1], [hour2], [hour3], [hour4])
VALUES
    ('2013-04-01 00:00:00', 1, 'Jim', 'test', 129, 343, 54, 89),
    ('2013-04-01 00:00:00', 2, 'Bob', 'rewe', 45, 6, 45, 2),
    ('2013-04-02 00:00:00', 3, 'Joe', 'fdf', 7, 8, 4, 3)

Select Query:

select date, 
  id, 
  name,
  replace(MightMouse, 'hour', '') hour,
  observationvalue
from tableB
unpivot
(
  observationvalue
  for MightMouse in (hour1, hour2, hour3, hour4)
) unpiv

I thought the usage of the replace function was as follows:

REPLACE ( string_expression , string_pattern , string_replacement )

string_expression

http://msdn.microsoft.com/en-us/library/ms186862.aspx

As per the definition of the replace function, string_expression is the string in which a sub-string is being searched (the sub-string can be the full string). For example,

replace('mynameisjohn', 'john', '')

This would search for the sub-string john in string_expression mynameisjohn and replace it with empty string, resulting in a string that equals mynameis.

But in the above example, I don't understand what MightyMouse is. There is no MightyMouse in the original table. I also don't know how the unpivot part fits into the query as in the flow of execution.

If this was python for example there is a flow to the logic of the code that is intuitive. With SQL, it seems you can build ugly queries and from sql's perspective things work just fine. But from the user's perspective it can be difficult to decompose what is going on in different parts of the query code.

Solution

UNPIVOT is a table operator, it operates on the table preceeding it:

tableName UNPIVOT (<unpivot-expression>)

Your UNPIVOT adds two extra columns (not one as has been implied), first the observationvalue column, which contains the values from the columns (hour1, hour2, hour3, hour4) rotated from horizontal to vertical.

And secondly it adds the MightMouse column which is the Names of the (hour1, hour2, hour3, hour4) columns that the corresponding current observationvalue was pulled from.

So this row:

hour1, hour2, hour3, hour4
129,   343,   54,    89

becomes these rows:

observationvalue MightMouse
129              hour1
343              hour2
 54              hour3
 89              hour4

(With the other corresponding column values also, of course)

Hopefully, this also makes clear what the REPLACE is doing and why it works.

As for the effective(*) order of execution: the FROM clause is always executed first in a SQL Query, then the WHERE clause. The SELECT clause (the column list and column expressions) is almost last, and an ORDER BY clause would normally be last (there are some MS specific exceptions).

(* - this is only the "effective"/"logical" order of execution. The SQL engine is allowed to do things in any actual order that it wants, so long as it has the same logical effect as this order)