Thursday, May 27, 2010

ENABLE/DISABLE TRIGGER must follow a semicolon

Can't get an ENABLE or DISABLE TRIGGER to even parse, much less execute? This might be the cause,

Due to a Microsoft glitch, ENABLE and DISABLE are not yet keywords. This means for them to be recognized as the beginning of a statement, the preceeding statement must end with a semi-colon. But putting it on the front of the offending line is clearer, to me:

;DISABLE TRIGGER dbo.trDoSomething ON MyTable

For more info, see: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=307937&wa=wsignin1.0

Tuesday, May 4, 2010

Using PIVOT - An (honest!) Simple Example

The concept of the PIVOT keyword is pretty simple: rotate the results so that the rows and columns are transposed. But the syntax is, well, kinda weird. This example uses PIVOT in the simplest way, to make it easier to pick through the code and see what's going on.

First let's create the example table, which contains one row for each girl. In our admittedly skewed sample set, all girls have cats, and there are only five girl names represented: Ada, Bea, Cat, Dot, and Eve. (If anyone knows of a three-letter girl's name beginning with "C", I'd be obliged.)

-- Drop and re-create the table. 
IF OBJECT_ID('dbo.Girl') IS NOT NULL DROP TABLE dbo.Girl

CREATE TABLE dbo.Girl
(
    GirlID INT IDENTITY(1,1), 
    Name       NVARCHAR(50),
    CatCount   TINYINT              
)

Now let's add some reasonable (except for one "crazy cat lady", apparently) data to the table.

-- Populate the table.  
INSERT INTO dbo.Girl
    (Name, CatCount)
VALUES
    ('DOT', 9), ('CAT', 3), ('CAT', 1), ('ANN', 3),
    ('ANN', 3), ('BEA', 3), ('EVE', 4), ('EVE', 3),
    ('EVE', 3), ('EVE', 4), ('EVE', 4), ('ANN', 3),
    ('EVE', 3), ('BEA', 5), ('EVE', 3), ('ANN', 1),
    ('EVE', 1), ('EVE', 3), ('EVE', 3), ('CAT', 103),
    ('BEA', 2), ('EVE', 3), ('CAT', 3), ('DOT', 2),
    ('DOT', 4), ('EVE', 3), ('DOT', 9), ('BEA', 2)

One of the common things we do with data like this is to create subtotals by value. In this case, we can find the total number of cats by girl-name.

-- Group by girls' name to see which name has the most cats. 
-- This displays the girls' names as rows.
SELECT Name           AS 'Girl''s Name',
       SUM(CatCount)  AS 'Total Cats'
  FROM dbo.Girl
 GROUP BY Name

The results would look like this:

-- Girl's Name Total Cats
   ANN          10
   BEA          12
   CAT          110
   DOT          24
   EVE          37

But by using the PIVOT keyword, we can also display the data with the girls' names as the columns. If you think about it, that's pretty cool: it turns data into metadata.

-- Get the same data, but with the girls' names as columns. 
SELECT  ANN  AS 'Total Cats: Ann',
        BEA  AS 'Total Cats: Bea',
        CAT  AS 'Total Cats: Cat',
        DOT  AS 'Total Cats: Dot',
        EVE  AS 'Total Cats: Eve'
  FROM
    (SELECT Name,
            CatCount
       FROM dbo.Girl) AS tSource
PIVOT
(
    SUM(CatCount)
    FOR Name IN (ANN, BEA, CAT, DOT, EVE)
) AS tPivoted

The results would look like this:

-- Total Cats: Ann    Total Cats: Bea    Total Cats: Cat    Total Cats: Dot    Total Cats: Eve
   10                 12                 110                24                 37

Of course, this capability comes at a price: you have to explicitly list the five girl names, which means you have to know what the values are in advance. Often, that's not a big problem: for example, the months of the year.