SQL Server 2005 PIVOT Operator

For my current project I needed to convert rows of data to columns. I learned how to do this with the SQL Server 2005 PIVOT Operator.

Here's the original code...

SELECT p.user_id, pit.data_name, pi.item_value
FROM profile p
JOIN profile_item pi ON pi.profile_id = p.profile_id
JOIN profile_item_type pit ON pit.profile_item_type_id = pi.profile_item_type_id
AND pit.data_name in ('age','gender','marital_status','family_size','household_income','ethnicity')
WHERE p.user_id = 100

...and the original results...

p.user_id  pit.data_name        pi.item_value
---------  -------------        -------------
100        age                  Under 18
100        gender               Male
100        marital_status       Single
100        family_size          1
100        household_income     Under $15,000
100        ethnicity            Rather not disclose

...and now the new SQL using the PIVOT operator...

SELECT *
FROM (SELECT p.user_id, pit.data_name, pi.item_value
          FROM profile p
          JOIN profile_item pi ON pi.profile_id = p.profile_id
          JOIN profile_item_type pit ON pit.profile_item_type_id = pi.profile_item_type_id
          AND pit.data_name in ('age','gender','marital_status','family_size','household_income','ethnicity')
WHERE p.user_id = 100)m
PIVOT (MAX(item_value) FOR data_name IN ([age],[gender],[marital_status],[family_size],[household_income],[ethnicity]))p

...and the desired results...

p.user_id  age        gender    marital_status   family_size  household_income   ethnicity
---------  ---        ------    --------------   -----------  ----------------   ---------
100        Under 18   Male      Single           1            Under $15,000      Rather not disclose

You could also cross tab this without using pivot operator...

SELECT p.user_id,
MAX(CASE WHEN pit.data_name='age' THEN pi.item_value ELSE NULL END) AS age,
MAX(CASE WHEN pit.data_name='gender' THEN pi.item_value ELSE NULL END) AS gender,
MAX(CASE WHEN pit.data_name='marital_status' THEN pi.item_value ELSE NULL END) AS marital_status,
MAX(CASE WHEN pit.data_name='family_size' THEN pi.item_value ELSE NULL END) AS family_size,
MAX(CASE WHEN pit.data_name='household_income' THEN pi.item_value ELSE NULL END) AS household_income,
MAX(CASE WHEN pit.data_name='ethnicity' THEN pi.item_value ELSE NULL END) AS ethnicity
FROM profile p
JOIN profile_item pi ON pi.profile_id = p.profile_id
JOIN profile_item_type pit ON pit.profile_item_type_id = pi.profile_item_type_id
AND pit.data_name in ('age','gender','marital_status','family_size','household_income','ethnicity')
WHERE p.user_id = 100
GROUP BY p.user_id

My Netvibes Flex Tab

My Netvibes Flex Tab is a collection of Adobe Flex feeds.
To add it to you Netvibes account click the image below.
 

How to change focus with the enter key

Here is a simple Flex app that shows how to change the focus of of several textInput components with the enter key. The one thing I have not yet figured out is why, after the Alert window is closed, the focus jumps back to the login button instead of the "fullName" text input field.

enterKeyTest.mxml

<?xml version="1.0"?>
<mx:Application xmlns:mx="http://www.adobe.com/2006/mxml" xmlns:local="*">
    <local:focusLoop />
</mx:Application>

focusLoop.mxml

<?xml version="1.0" encoding="utf-8"?>
<mx:VBox xmlns:mx="http://www.adobe.com/2006/mxml" creationComplete="initApp()">
    <mx:Script>
    <![CDATA[
        ////////////////////////////////////////////////////////////
        import mx.events.CloseEvent;
        import mx.core.UITextField;
        import flash.ui.Keyboard;
        import mx.controls.Alert;
        ////////////////////////////////////////////////////////////
        private function initApp():void
        {
            this.addEventListener(KeyboardEvent.KEY_UP, keyHandler);
        }
        ////////////////////////////////////////////////////////////
        private function keyHandler(event:KeyboardEvent):void
        {
            if(event.keyCode == Keyboard.ENTER)
            {
                if(event.target is UITextField)
                    drawFocus(true);
                else if(event.target.id == "myBtn")
                    showAlert();
            }
        }
        ////////////////////////////////////////////////////////////
         override public function drawFocus(isFocused:Boolean):void
        {
            focusManager.getNextFocusManagerComponent().setFocus();
        ////////////////////////////////////////////////////////////
        }       
        private function showAlert():void
        {
            Alert.show('Clicked Login Button!', '', Alert.OK, this, alertListener);           
            myBtn.focusEnabled=false;
        }
        ////////////////////////////////////////////////////////////
        private function alertListener(eventObj:CloseEvent):void
        {
            fullName.setFocus();
        }   
    ]]>
    </mx:Script>
        <mx:Form>
        <mx:FormItem label="Name">
            <mx:TextInput id="fullName" width="100" focusOut="myBtn.focusEnabled=true"/>
        </mx:FormItem>
        <mx:FormItem label="Street">
            <mx:TextInput width="100" />
        </mx:FormItem>
        <mx:FormItem label="City">
            <mx:TextInput width="100" />
        </mx:FormItem>
        <mx:FormItem label="State">
            <mx:TextInput width="50" maxChars="2" />
        </mx:FormItem>
        <mx:FormItem label="Zip Code">
            <mx:TextInput width="50" maxChars="5" />
        </mx:FormItem>
        <mx:FormItem>
            <mx:Button id="myBtn" label="Login" />
        </mx:FormItem>
    </mx:Form>
</mx:VBox>

FMS - What I'm Learning about Client and Server Scripts

I have an MXML/AS3 component that calls some remote FMS¹ methods, which works well. However, the client callbacks are not working.

Being an FMS newbie, I'm wondering if this is an AS2/AS3² issue. So, I turned to the O'Reilly book Programming Flash Communication Server to learn more.
In the pursuit of getting client callbacks to work, here is some of what I've learned about FMS and client scripting so far... 
Client-To-Server Calls

• Each SWF that connects to FMS is called "The Client" and is referenced by the SSAS³ code via the "Client object."
  
  
• Clients use the NetConnection.call(...) method to ask the server to do something for them. For the call to work the method on the server must have the same name and it must be attached to the server-side Client Object.
  For example...
  
  nc.call("getStreamLength", resultObject, name);
  
  - Where nc is the NetConnection object used to connect the client to FMS.
  - To process the results a handler method should to be defined.
  
  
• Server-side methods must to be attached to the Client object to be invoked by a SWF (the Client).
  For example...
  
  // The new function object is attached to the client object
  // so that it can be called by the client.
  clientSWF.getStreamLength = function(name)
  {
    return Stream.length(name);
  };
  
  
• SSAS also allows you to attach methods to the "Client.prototype" object⁴. This helps save memory and processor time on the server and every instance of that class (I.e. the Client class) will contain the attached methods automatically. For example...
  Client.prototype.getStreamLength = function (name)
  {
    return Stream.length(name);
  };
  
  
• To sum up...
  
  • You can attach SSAS methods needed by every Client object to Client.prototype.
  • Alternatively, you can define SSAS methods on specific client instances. This is how you can allow access to specific SSAS methods for a subset of clients connected to the application.

Server-To-Server Call

• For the server to call a client-side method, the method must be attached to a client-side NetConnection object.
  For example...
  
  nc.setStreamLength = function(value)
  {
     this.streamLength = value;
  };

This is where I think my problem is...
Somehow I have to attach my AS3 client-side method to the NetConnection object. But, the NetConnection object is a sealed class in AS3 (AS2 it is a dynamic class). So, I created a dynamic class (named "netConnect") that extends the NetConnection class and now I need to figure out the syntax to add an AS3 function object to a dynamic class.
(2007-04-25 - 3:30 PM CST)

The solution was pretty easy...
I just added a dynamic property to my netConnect class and then assigned my function statement to it.
(2007-04-25 - 5:26 PM CST)

Client-Side AS3:

/////////////////////////////////////////
private var nc:NetConnect = new NetConnect();
/////////////////////////////////////////
public function onCreationComplete():void
{

   var myTimer:Timer = new Timer(1000, 0);
   myTimer.addEventListener("timer", createConnection);
   myTimer.start();

}
/////////////////////////////////////////
private function createConnection():void
{
   if(!nc.connected)
   {
      nc.objectEncoding = flash.net.ObjectEncoding.AMF0;
      nc.connect("rtmp:/testCallback");       // Local FMS Application
      nc.update = callBack;                       // Assign callBack() function
      this.connect(nc);
      nc.addEventListener(NetStatusEvent.NET_STATUS, netStatusHandler);
   }
}
/////////////////////////////////////////
public function callBack():void
{
   trace( "** The callBack() function was called by the server **" );
}


Server-Side AS2:
...
clientSWF.call("update", null);
...

^{FMS - Flash Media Server
AS2/AS3 - ActionScript Ver. 2 or 3
SSAS - Server Side ActionScript
The "Client.prototype" object is the base class of the Client object}

Multiple "effectEnd" events from a parallel effect

I created a parallel effect with AS3...

var zoom:Zoom = new Zoom();
zoom.easingFunction = Exponential.easeIn;
zoom.duration = 1500;
zoom.zoomHeightFrom = 0.0;
zoom.zoomHeightTo = 1.0;

var fade:Fade = new Fade();
fade.easingFunction = Exponential.easeIn;
fade.alphaFrom = 0;
fade.alphaTo = 1;
fade.duration = 1500;
msgEffect = new Parallel();
msgEffect.targets = effectsArray;
msgEffect.addChild(zoom);
msgEffect.addChild(fade);
msgEffect.play();

I added a listener for when the effect ends...

addEventListener(EffectEvent.EFFECT_END, close);

Then I coded the following handler...

private function close(event:EffectEvent=null):void
{
visible = false;
mx.managers.PopUpManager.removePopUp(this);
dispatchEvent(closeEvent);
}

Even though the effects are part of a parallel effect sequence object, two "effectEnd" events are fired one for the Zoom and the other for the Fade.

I believe this occurs because the targets fire the "effectEnd" event, not the parallel effect sequence object.

It looks like I need some wrapper that says, "Hey, both effects in your parallel effect sequence object are done!" Seems odd though, shouldn't the parallel sequence object do that?

Well, here's my very cheesy fix for this problem. I changed the event handler as follows...

private function close(event:EffectEvent=null):void
{
  visible = false;
  mx.managers.PopUpManager.removePopUp(this);
  dispatchEvent(closeEvent);

  // Ignore any other "effectEnd" events.
  removeEventListener(EffectEvent.EFFECT_END, close);
}

Fading buttons and embedded fonts

To apply a fade effect to text you must use embedded fonts. I was going nuts trying to use an embedded font with buttons so the applied fade effect would work. After several failed attempts I found this post (thanks Jesse!).

In short, create a style that looks like this...
Button
{
embedFonts: true;
fontWeight: normal;
fontSize: 22;
fontFamily: YourEmbeddedFont;
}

Adobe Flex 2.0 » VideoDisplay Borders - Part I

Rectangular borders are no problem for the Flex "VideoDisplay" component. However, radiused borders seem to be.

VideoDisplay is a composite component; An Actionscript class that extends UIComponent and adds a child "flash.media.Video" component. It contains a method named "createBorder()" that loads a border skin and adds it as a child underneath all other child components...

addChildAt(DisplayObject(border), 0);

As a result, the rectangular "flash.media.Video" component is on top of the border and the corners of the video rectangle overlay and extend beyond the VideoDisplay's radiused corners.

How can I get around this problem? I don't know yet, but I'm working on it!